[MDEV-5253] Foreign key doesn't always require index on referenced columns Created: 2013-11-07 Updated: 2013-12-02 Resolved: 2013-12-02 |
|
| Status: | Closed |
| Project: | MariaDB Server |
| Component/s: | None |
| Affects Version/s: | 5.5.33a |
| Fix Version/s: | None |
| Type: | Bug | Priority: | Minor |
| Reporter: | Donnie Pinkston | Assignee: | Elena Stepanova |
| Resolution: | Not a Bug | Votes: | 0 |
| Labels: | upstream | ||
| Attachments: |
|
| Description |
|
I discovered a situation where a two-column foreign key constraint doesn't seem to require an index on the referenced columns in the referenced table. I am attaching a repro case that illustrates the scenario, but it is rather complex. Basically, there are three tables:
Based on whether table B's foreign key to A is declared inline or separately, table C cannot/can be created. |
| Comments |
| Comment by Elena Stepanova [ 2013-12-02 ] |
|
Hi, What you observe is actually a documented behavior, although not quite obvious. Here is what happens here:
If you run SHOW CREATE TABLE b1, you'll see the index on the column. So, when you later create c1, it works all right since there is an index.
If you run SHOW CREATE TABLE b2, you'll see there is no FK constraint on the table. One thing that might be considered a documentation bug here is the vague wording in the second quote. It says that the inline references are not "recognized" or "accepted", while in fact they don't cause a syntax error, but are just ignored. If you wish, you could suggest a better wording to MySQL documentation team at bugs.mysql.com. Please comment if you have further concerns on the topic. |