1

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.

Improve this question

3 Answers 3

Reset to default
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.

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

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?
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.
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.
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)
@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.
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

Improve this answer

1 Comment

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.)
0

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

Improve this answer

3 Comments

Okay, means only a primary key of one table can be a foreign key on the second table?
Precisely, 2nd table can have a foreign key if the key exists in the 1st table and is a primary key.
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.)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.