Timeline for Standard Behaviour for mix of 'ON-DELETE-CASCADE' and 'ON-DELETE-RESTRICT' constraints in Mysql
Current License: CC BY-SA 4.0
17 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| May 10, 2019 at 19:54 | vote | accept | Dinesh Kumar | ||
| May 10, 2019 at 13:04 | vote | accept | Dinesh Kumar | ||
| May 10, 2019 at 14:39 | |||||
| May 7, 2019 at 13:14 | vote | accept | Dinesh Kumar | ||
| May 10, 2019 at 13:04 | |||||
| May 2, 2019 at 0:40 | comment | added | Rick James | Sometimes FKs are more hassle then they are worth. | |
| Apr 30, 2019 at 6:38 | comment | added | Lennart - Slava Ukraini | @Akina, I was not referring how it's actually done, just pointing out that there are other possibilities than ordering constraints lexicographical. Besides, changing the order of constraints inside a create table statement may or may not change the order in which they are created. I suggest creating them with alter table and wait a while to see if it matters | |
| Apr 30, 2019 at 5:01 | comment | added | Akina |
@Lennart Swap whole FK lines in 1st test (c before b)...
|
|
| Apr 29, 2019 at 18:00 | history | tweeted | twitter.com/StackDBAs/status/1122923624846041088 | ||
| Apr 29, 2019 at 12:18 | comment | added | Lennart - Slava Ukraini | @Akina, the order in which they are created (create time) may be a possible alternative to lexical order | |
| Apr 29, 2019 at 12:16 | answer | added | Lennart - Slava Ukraini | timeline score: 1 | |
| Apr 29, 2019 at 11:43 | comment | added | Lennart - Slava Ukraini | Example 2 is not allowed either, for the same reason | |
| Apr 29, 2019 at 11:00 | comment | added | Lennart - Slava Ukraini |
I don't know what the standard says, but I tested the first example with Db2 V11.1.4.4, and Db2 does not allow creation of table D because of: SQL20255N FOREIGN KEY "ID..." is not valid because it would cause a descendent table "DB2INST1.D" to be delete-connected to its ancestor table "DB2INST1.A" through multiple relationships with conflicting delete rules. The conflict is between the delete rules of constraints "DB2INST1.D.SQL190429125" and "DB2INST1.D.ID..." on the descendent table. Reason code = "3". SQLSTATE=42915
|
|
| Apr 29, 2019 at 10:21 | comment | added | Akina | 'lexical order based on their name' mean 'Iexical order of table names' , am I right? I cannot find any direct reference in user manual... except InnoDB and FOREIGN KEY Constraints: InnoDB performs cascading operations through a depth-first algorithm, based on records in the indexes corresponding to the foreign key constraints. This can explain (to self at least) why the first code succeeded whereas second one fails. Simply assume that the chained actions calculated for a table are calculated once. | |
| Apr 29, 2019 at 9:04 | comment | added | Dinesh Kumar | @Akina, 'lexical order based on their name' mean 'Iexical order of table names' , am I right? | |
| Apr 29, 2019 at 9:01 | comment | added | Dinesh Kumar | @Akina, can you elaborate this behaviour in Postgres? | |
| Apr 29, 2019 at 8:26 | comment | added | Akina | I strongly suspect that the foreign keys are "applied" in lexical order based on their name. Of course. The only alternative is random order, but it makes no sense at all. | |
| Apr 29, 2019 at 7:52 | history | edited | Dinesh Kumar | CC BY-SA 4.0 |
edited title
|
| Apr 29, 2019 at 7:38 | history | asked | Dinesh Kumar | CC BY-SA 4.0 |