MySQL Error Code 1215: Cannot add foreign key Constraint

Question

I'm very new to SQL, I'm trying to define a 2 tables Hospital and Hospital_Address but when I'm trying to add foreign key in Hospital_Address it throws an error: "1215: Cannot add foreign key"

create table Hospital (
             HId Int not null,  
           HName varchar(40) not null, 
           HDept int, Hbed Int, 
         HAreaCd int not null, 
         Primary Key (HId)
                      );

create table Hospital_Address (
                HAreaCd Int not null,
                  HArea varchar(40) not null,
                  HCity varchar(40), 
                  HAdd1 varchar(40),
                  HAdd2 varchar(40), 
                  Primary Key (HArea), 
                 foreign key (HAreaCd) references Hospital (HAreaCd));

Please help me in this regard. Thanks in advance.

spencer7593 · Accepted Answer · 2014-07-09T21:16:58.337

5

MySQL requires that there be an index on the HAreaCd column in the parent Hospital table, in order for you to reference that column in a FOREIGN KEY constraint.

The normative pattern is for the FOREIGN KEY to reference the PRIMARY KEY of the parent table, although MySQL extends that to allow a FOREIGN KEY to reference a column that is a UNIQUE KEY, and InnoDB extends that (beyond the SQL standard) and allows a FOREIGN KEY to reference any set of columns, as long as there is an index with those columns as the leading columns (in the same order specified in the foreign key constraint.) (That is, in InnoDB, the referenced columns do not need to be unique, though the behavior with this type of relationship may not be what you intend.)

If you create an index on that column in Hospital table, e.g.:

CREATE INDEX Hospital_IX1 ON Hospital (HAreaCd);

Then you can create a foreign key constraint that references that column.

However, because this is a non-standard extension of MySQL and InnoDB, the "best practice" (as other answers here indicate) is for a FOREIGN KEY to reference the PRIMARY KEY of the foreign table. And ideally, this will be a single column.

Given the existing definition of the Hospital table, a better option for a foreign key referencing it would be to add the Hid column to the Hospital_Address table

... ADD HId Int COMMENT 'FK ref Hospital.HId'

... ADD CONSTRAINT FK_Hospital_Address_Hospital 
       FOREIGN KEY (HId) REFERENCES Hospital (HId)

To establish the relationship between the rows, the values of the new HId column will need to be populated.

edited Jul 09 '14 at 21:16

answered Jul 09 '14 at 21:05

spencer7593

106,611
15
112
140

Thanks Spencer, after defining Hospital_IX1 I was able to define the Hospital_Address table. But as a noob I'm confused why did we define a New Index Hospital_IX1... and when we create it how does Hospital_Address table know that the index is Hospital_IX1. And what happens to the Primary Key Indexing of the Hospital_Address? – ankurind Jul 09 '14 at 21:31
InnoDB **requires** an INDEX to be defined on the columns referenced by a FOREIGN KEY. (Underneath the covers, InnoDB needs that index so it can enforce the constraint; absent that index, InnoDB would have to do some ugly concurrency-killing entire-table locking; but InnoDB doesn't even go there, it just requires a suitable index to be available. MySQL doesn't care what the name of the index is; it just checks that a suitable index exists, in this case, any index that has `HAreaCd` as the leading column. The information about the tables, indexes, constraints, etc. all gets recorded in the db. – spencer7593 Jul 09 '14 at 21:59
Adding a second index to the table doesn't affect the existing indexes on the table; the PRIMARY KEY is still the PRIMARY KEY, any UNIQUE KEY is still a UNIQUE KEY; any existing indexes are still indexes. MySQL accesses the information about available indexes and uniqueness, data distribution and cardinality, whenever we run a query that references the table, so the optimizer can choose an appropriate access plan. – spencer7593 Jul 09 '14 at 22:01
Thanks, this make things more clear. The Foreign Key should be a leading column in any Index would suffice it to be declared as Foreign Key and the column preferably should be the Primary Key of he parent table in InnoDB (But yes not necessary) – ankurind Jul 09 '14 at 22:08
@user3822147. Yes, exactly. Note that with a NON-UNIQUE index, there can be multiple rows in parent `Hospital` table with the same value in `HAreaCd`; the foreign key references to that are "weird" in that the relationship is between the row in the child table and _any one_ of the matching rows in the parent table. If `HAreaCd` is _unique_ in the `Hospital` table, you can add the `UNIQUE` keyword when adding the index... `CREATE UNIQUE INDEX...`, then the relationship would be to one specific row. – spencer7593 Jul 09 '14 at 22:13

score 1 · Answer 2 · edited May 23 '17 at 11:46

1

You cannot add a foreign key to a non-primary key element of another table usually.

If you really need to do so, refer to this question for help : Foreign Key to non-primary key

edited May 23 '17 at 11:46

Community

1
1

answered Jul 09 '14 at 20:59

CoqPwner

913
1
11
24

Actually, InnoDB allows a foreign key constraint to reference any set of columns, as long as there is an appropriate index defined on that set of columns. This is an extension beyond the SQL standard; the normative pattern is to reference the PRIMARY KEY; but it's also possible reference a UNIQUE KEY as well. (When a FOREIGN KEY references a non-unique set of columns, the behavior can be unexpected, if not understood.) – spencer7593 Jul 09 '14 at 21:06

score 0 · Answer 3 · answered Jul 09 '14 at 20:58

0

HAreaCd in the Hospital table should be a primary key. Only then can you reference it in the Hospital_Address table

answered Jul 09 '14 at 20:58

RingMaster

3
4

Okay, means only a primary key of one table can be a foreign key on the second table? – ankurind Jul 09 '14 at 21:00
Precisely, 2nd table can have a foreign key if the key exists in the 1st table and is a primary key. – RingMaster Jul 09 '14 at 21:01
Actually, InnoDB allows a for any set of columns in a table to be referenced by a FOREIGN KEY, as long as there is an appropriate index defined. The normative pattern is for a foreign key to reference the PRIMARY KEY or a UNIQUE KEY of the table, but InnoDB extends beyond the SQL standard, and allows a non-unique set of columns to be referenced. (This is non-standard behavior, and if not understood well, the behavior of such a FOREIGN KEY can be unexpected and counter-intuitive.) – spencer7593 Jul 09 '14 at 21:13

MySQL Error Code 1215: Cannot add foreign key Constraint

3 Answers3

Linked