CODEWITHSUNDEEP
mysqlsql
ankurind
ankurind

Reputation: 25

MySQL Error Code 1215: Cannot add foreign key Constraint

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.

Upvotes: 1

Views: 6484

Answers (3)

spencer7593
spencer7593

Reputation: 108370

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.

Upvotes: 5

CoqPwner
CoqPwner

Reputation: 964

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

Upvotes: 1

RingMaster
RingMaster

Reputation: 3

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

Upvotes: 0

Related Questions