MySQL Forums
Forum List  »  InnoDB

Re: Strange Foreign key error
Posted by: Peter Brawley
Date: April 12, 2016 07:02PM

Is "SGDB' an abbreviation of "relational database" in French?

You have:

select * from tbparent;
+-------+-------+
| colp1 | colp2 |
+-------+-------+
| a     |     0 |
| a     |     1 |
+-------+-------+
select* from tbchild;
+--------+--------+--------+
| colch1 | colch2 | colch3 |
+--------+--------+--------+
|    100 | a      | abc    |
+--------+--------+--------+

Ugh. Which tbparent row is the parent row of tbchild row?

An InnoDB anomaly, to put it mildly, is that it permits non-unique FKs. You've found a serious side-effect of that anomaly.

The docs say "The handling of foreign key references to nonunique keys or keys that contain NULL values is not well defined ... You are advised to use foreign keys that reference only UNIQUE (including PRIMARY) and NOT NULL keys."

Good advice, as you've found.

Options: ReplyQuote


Subject
Views
Written By
Posted
1825
April 12, 2016 03:23PM
Re: Strange Foreign key error
703
April 12, 2016 07:02PM
686
April 13, 2016 08:57AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.