CODEWITHSUNDEEP
mysqldatabase-designforeign-keysmany-to-manyconstraints
dryhay
dryhay

Reputation: 79

MySQL "many to many" relation error

I've got 3 tables in "many to many" relation.

Table A:

CREATE TABLE IF NOT EXISTS tabA(
tabAcolA mediumint unsigned not null auto_increment,
tabAcolB text not null,
tabAcolC text null,
PRIMARY KEY keyA (tabAcolA)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Table B:

CREATE TABLE IF NOT EXISTS tabB(
tabBcolA mediumint unsigned not null auto_increment,
tabBcolB text not null,
PRIMARY KEY keyB (tabBcolA)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Table C (AB joint):

CREATE TABLE IF NOT EXISTS tabC(
    tabCcolA int unsigned not null,
    tabCcolB int unsigned not null,
    PRIMARY KEY keyC (tabCcolA,tabCcolB),
    CONSTRAINT FkeyA FOREIGN KEY (tabCcolA) 
    REFERENCES tabA(tabAcolA) ON DELETE RESTRICT ON UPDATE RESTRICT,
    CONSTRAINT FkeyB FOREIGN KEY (tabCcolB) 
    REFERENCES tabB(tabBcolA) ON DELETE RESTRICT ON UPDATE RESTRICT
    ) ENGINE=InnoDB

It returns an error:

1215 Cannot add foreign key constraint

What is wrong there?

Upvotes: 0

Views: 41

Answers (1)

spencer7593
spencer7593

Reputation: 108390

What's wrong is the datatype mismatch of the columns.

The datatype of the foreign key column must be an exact match to the referenced column. e.g.

 fkcol  INT UNSIGNED     ref -> pkcol  INT UNSIGNED

or 

 fkcol  MEDIUMINT        ref -> pkcol  MEDIUMINT

If we attempt to create a foreign key constraint with columns of different datatypes, InnoDB returns an error. (The behavior observed by OP.)

Upvotes: 1

Related Questions