1

I have the following parent table:

create table A(
  a int(64) unsigned not null,
  b set('a', 'b', 'c') not null default '',
  c set('d', 'e') not null default '',
  primary key ( a,b,c)
)

And child:

create table B(
  d int(64) unsigned not null auto_increment,
  a int(64) unsigned not null,
  c set('d', 'e') not null default '',
  primary key (d),
  key fk1 (a,c),
  constraint fk1 foreign key (a, c) references (a, c)
)

But then I get an fk error on creating the child table in mysql logs:

Error in foreign key constraint of table Foreign key (a,c) references A (a,c): Cannot find an index in the referenced table where the referenced columns appear as the first columns, or column types in the table and the referenced table do not match for constraint.

What is incorrect with my SQL?

user1561108
  • 2,666
  • 9
  • 44
  • 69
  • You have single quotes around the column names. I consider this a typo and vote to close such questions. – Gordon Linoff Mar 04 '17 at 14:01
  • @GordonLinoff no I don't that was a typo. Ty for formatting – user1561108 Mar 04 '17 at 14:04
  • 1. The referenced table name is missing. 2. There is no index on the referenced table that can support the FK. 3. Referencing columns without a unique index on the same columns (for the referenced table) is a strange design. – Paul Spiegel Mar 04 '17 at 15:52
  • Related: http://stackoverflow.com/questions/588741/can-a-foreign-key-reference-a-non-unique-index – Paul Spiegel Mar 04 '17 at 15:59

1 Answers1

4

When you define a foreign key, MySQL requires that an index exists on the column(s) in the remote table, so that it can perform the constraint check efficiently.

It can be an index on referenced columns exactly, or an index which starts with the referenced columns, and contains other columns afterwards, So in your case, table A should have an index with columns (a,c) and possible some others.

Right now, in table A there's an index on the set of columns (a, b, c). Note that the order of columns is important, and an index on (a,b,c) would not be the same as an index in (a,c,b).

The FK references columns (a,c). There's no index in table A for these columns, or any other index which starts with these two and contains more columns after that.

So you have two choices:

  1. Modify the PK in table A to be (a,c,b) instead of (a,b,c). Note that in some cases this might have performance consequences for your queries, since some of them might not be able to use an index.

  2. Add an additional index in A for just these two columns:

    ALTER TABLE A
    ADD INDEX tempindex (a, c);

Yossi Vainshtein
  • 3,845
  • 4
  • 23
  • 39
  • even though the solution is in the error log, it would have taken me forever to work it out. thanks @YossiVainshtein – user1561108 Mar 05 '17 at 00:11