tag:blogger.com,1999:blog-9144505959002328789.post2919849909076540571..comments2024-03-28T11:39:50.622+01:00Comments on Karlsson on databases and stuff: The forgotten FOREIGN KEY constraintKarlssonhttp://www.blogger.com/profile/04874338187076980133noreply@blogger.comBlogger7125tag:blogger.com,1999:blog-9144505959002328789.post-63979166937226729982021-12-27T08:17:53.558+01:002021-12-27T08:17:53.558+01:00The contents existing at this site are truly remar...The contents existing at this site are truly remarkable for people knowledge, well, keep up the good work fellows. <a href="https://www.789bet.biz/casino/" rel="nofollow"> คาสิโนออนไลน์ </a> Casino-789bettinghttps://www.blogger.com/profile/06493578080795318681noreply@blogger.comtag:blogger.com,1999:blog-9144505959002328789.post-74203468535652272512021-07-30T10:09:35.362+02:002021-07-30T10:09:35.362+02:00Thank you for posting such a great article! It con...Thank you for posting such a great article! It contains wonderful and helpful posts. Keep up the good work<br /><br /><a href="https://www.bipard.org/2021/05/sha-punjab-ayushman-bharat-sarbat-sehat.html" rel="nofollow">sha punjab | Ayushman Bharat Sarbat Sehat Bima Yojana 2021 - Online Registration, Eligibility Criteria, Beneficiary Search, SHA Login</a>Yakshitahttps://www.blogger.com/profile/05459704732582443449noreply@blogger.comtag:blogger.com,1999:blog-9144505959002328789.post-34308894437800993922009-02-10T02:38:00.000+01:002009-02-10T02:38:00.000+01:00I found this store that perfoms a drop table with ...I found this store that perfoms a drop table with cascade constraint for a given table:<BR/><BR/>http://laboticadellanga.blogspot.com/2009/01/drop-table-cascade-constraints-sql.htmlChristianhttps://www.blogger.com/profile/05227437919341765612noreply@blogger.comtag:blogger.com,1999:blog-9144505959002328789.post-77242566122309385752008-12-18T14:58:00.000+01:002008-12-18T14:58:00.000+01:00I'm from the other school of thought you mentioned...I'm from the other school of thought you mentioned - people who like to disable foreign keys. I just thought I'd expand a little on my reasoning:<BR/><BR/>- Having foreign keys increases your locking - and as a result decreases concurrency.<BR/><BR/>- If your engine is transactional/ACID compliant, then you (hopefully) shouldn't end up with inconsistencies unless you have some sort of logic error (it happens!). Based on this, some choose to enable foreign keys during development, disable them in heavy production.<BR/><BR/>- The InnoDB implementation is a bit ugly. It's missing a few features. One smaller one I'd like is deferrable constraints, so that if I am committing a chunk of work the checks only have to pass on commit (to understand how much of an issue this is, look what mysqldump has to do to restore data if you have foreign keys).Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-9144505959002328789.post-55398549468501993362008-12-18T14:46:00.000+01:002008-12-18T14:46:00.000+01:00Hi!"We all know what a FOREIGN KEY constraint is, ...Hi!<BR/><BR/>"We all know what a FOREIGN KEY constraint is, right? A reference from a column or set of columns in one table to the PRIMARY KEY in another table?"<BR/><BR/>Actually, the term "foreign key" just means that a column or collection of columns holds values that appear as key, that is, identifiers of another table. So, the columns are meant for storing key entries, but key entries from another table - hence, 'foreign' key. 'Foreign key' is a semantic term.<BR/><BR/>The term 'foreign key constraint' is simply an enforced rule that ensures the values are in fact valid keys of the other table: A foreign key is a way to maintain referential integrity.<BR/><BR/>Innodb is interesting in that it does *not* enforce that the foreign key constraint points to PRIMARY KEY or UNIQUE constraint columns. As long as the referenced columns (that is, columns in the 'parent') form the prefix of any index, InnoDB will accept the foreign key definition. Rather strange as it allows many parent rows to exist for a single child row (as well as of course many child rows per parent row)<BR/><BR/>Kind regards,<BR/><BR/>Rolandrpboumanhttps://www.blogger.com/profile/13365137747952711328noreply@blogger.comtag:blogger.com,1999:blog-9144505959002328789.post-8294837435424323792008-12-18T14:41:00.000+01:002008-12-18T14:41:00.000+01:00Sorry, clicked too fast...The above is true, of co...Sorry, clicked too fast...<BR/>The above is true, of course, only if the *is* a unique key to be used, otherwise InnoDB creates its own PRIMARY KEY (based on the internal row_id, I believe)<BR/><BR/>RegardsShlomi Noachhttps://www.blogger.com/profile/11874165719204714241noreply@blogger.comtag:blogger.com,1999:blog-9144505959002328789.post-35449722019856899482008-12-18T14:39:00.000+01:002008-12-18T14:39:00.000+01:00Hi,Nice post,One comment though: InnoDB does treat...Hi,<BR/><BR/>Nice post,<BR/>One comment though: InnoDB does treat UNIQUE keys as candidate keys: if no PRIMARY KEY has been defined for your InnoDB table, InnoDB selects the first UNIQUE KEY and uses it.<BR/><BR/>Regards,<BR/>Shlomi NoachShlomi Noachhttps://www.blogger.com/profile/11874165719204714241noreply@blogger.com