=> See below the last edit, things are moving on this domain They do not activate for changes in views, nor by changes to tables made by APIs that do not transmit SQL statements to the MySQL Server MySQL triggers activate only for changes made to tables by SQL statements. So if you were over confident enough in that engine to use triggers you should avoid cascades constraints. MySQL has one big documented limitation with cascades. Now I think I should add one important warning. It's been a long time since I wrote that. with some ON DELETE CASCADE for track tables (logs-not all logs-, things like that) and ON DELETE SET NULL when the master table is a 'simple attribute' for the table containing the foreign key, like a JOB table for the USER table. Usually my default is: ON DELETE RESTRICT ON UPDATE CASCADE. If Null is your value for users with no company this can be a good behavior, for example maybe you need to keep the users in your application, as authors of some content, but removing the company is not a problem for you. ON DELETE SET NULL : handful : if you delete a COMPANY row the related USERs will automatically have the relationship to NULL.This is dangerous but can be used to make automatic cleanups on secondary tables (so it can be something you want, but quite certainly not for a COMPANYUSER example) ON DELETE CASCADE : dangerous : if you delete a company row in table COMPANY the engine will delete as well the related USERs.ON DELETE RESTRICT : the default : if you try to delete a company_id Id in table COMPANY the engine will reject the operation if one USER at least links on this company, can save your life.I cannot see any interesting thing to do with that on an update, but I may be wrong. ON UPDATE SET NULL : if you update a company_id in a row of table COMPANY the engine will set related USERs company_id to NULL (should be available in USER company_id field). The engine will track the changes for you, it's good. ON UPDATE CASCADE : the best one usually : if you update a company_id in a row of table COMPANY the engine will update it accordingly on all USER rows referencing this COMPANY (but no triggers activated on USER table, warning).ON UPDATE NO ACTION : same as RESTRICT.ON UPDATE RESTRICT : the default : if you try to update a company_id in table COMPANY the engine will reject the operation if one USER at least links on this company.We'll take an example, a company table with a user table containing people from theses company CREATE TABLE COMPANY (įOREIGN KEY (company_id) REFERENCES COMPANY (company_id) ON. With MySQL you do not have advanced constraints like you would have in postgreSQL but at least the foreign key constraints are quite advanced. Especially if you have several applications requesting it (or just one application but with a direct mode and a batch mode using different sources). You'll be sure to have a consistent database, and that's one of the good reasons to use a database. Do not hesitate to put constraints on the database.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |