CODEWITHSUNDEEP
mysql
Tony-M
Tony-M

Reputation: 121

how can i prevent recurrent execution of query like ALTER TABLE....?

I`ve have Billing with MySQL database with 10Gb per month of new data. Billing has production and development version.

Programmers create patches and when prod in updating every patch is applying one by one. It is not the best way of DB patching but at this moment we can do nothing with it.

we have query like 

ALTER TABLE  `Customer` ADD FOREIGN KEY (  `contract_id` ) REFERENCES  `Document` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION ;

and i found that every time patch with this query aplying it create reference between tables. When there is mo then 100 000 000 records in table it is very slow and wrong.

Can i do somethig, that will prevent second execution of this query? may be give some name to reference &

Upvotes: 0

Views: 43

Answers (1)

BaBL86
BaBL86

Reputation: 2622

You can use this style:

IF NOT EXISTS (SELECT NULL FROM information_schema.TABLE_CONSTRAINTS WHERE
               CONSTRAINT_SCHEMA = DATABASE() AND
               CONSTRAINT_NAME   = 'contract_document' AND
               CONSTRAINT_TYPE   = 'FOREIGN KEY') THEN
    ALTER TABLE `Customer` ADD CONSTRAINT `contract_document`
                         FOREIGN KEY (`contract_id`)
                         REFERENCES `Document` (`id`);
END IF

Upvotes: 2

Related Questions