MySQL - Cannot add foreign key to table

I have a database using MySQL 2005. I have two tables, Enrolment and AlertMsg.

The primary keys for enrolment are two columns, UnitCode and StudentID. Both these two columns are foreign keys to another table.

The primary keys for AlertMsg are three columns, UnitCode, StudentID and AlertNo.

When I try to make a foreign key with UnitCode in the AlertMsg table, like so:

ALTER TABLE AlertMsg
ADD FOREIGN KEY (UnitCo开发者_运维问答de)
REFERENCES Enrolment(UnitCode)

I get the following error:

SQL Execution Error.

Executed SQL statement: ALTER TABLE AlertMsg

ADD FOREIGN KEY (UnitCode)
REFERENCES Enrolment (UnitCode)
Error Source: .Net SqlClient Data Provider
Error Message: There are no primary or candidate keys in the referenced table 'Enrolment' that match the referencing column list in the foreign key 'FK_AlertMsg_UnitCo_571DF1D5'.
Could not create constraint. See previous errors.

After some searching it seems that this is because UnitCode isn't a primary key of Enrolment. But Enrolment's table definition seems to show that it is. I'm a bit of a newbie at SQL, so I assume that if the far left column of the table definition has a key in it, it means its a primary key of the table.

Can anyone help? Thanks in advance.

---

The primary keys for enrolment are two columns, UnitCode and StudentID. Both these two columns are foreign keys to another table.

The primary keys for AlertMsg are three columns, UnitCode, StudentID and AlertNo.

You say "the primary keys for ...". Well actually it is probably not multiple keys but one key that is create using multiple columns. Those columns together is what needs to be unique in your table and therefore you can not just reference one of the columns in a foreign key.

If you want this statement to work

ALTER TABLE AlertMsg
ADD FOREIGN KEY (UnitCode)
REFERENCES Enrolment(UnitCode)

The values in UnitCode in table Enrolment needs to be unique and you need to add a unique constraint on that column. I guess that is not the case so you can't add the necessary unique constraint.

You should probably use this instead:

ALTER TABLE AlertMsg
ADD FOREIGN KEY (UnitCode, StudentID)
REFERENCES Enrolment(UnitCode, StudentID)

---

Standard primary keys don't have to be the leftmost indexed column in a table, it's just a common habit that developers and DBA's who define the table put the primary key column(s) first.

In standard SQL, a foreign key must reference:

• Column(s) defined as a PRIMARY KEY or UNIQUE KEY in the referenced table.
• The columns in the foreign key don't have to have the same names, but they must be the same in number, order, and data type.

I suspect you're really using Microsoft SQL Server 2005, not MySQL. There's no such product release known as "MySQL 2005".

---

You can try:

• Add foreign key constraint to 2 columns (UnitCode,StudentID) of Enrolment table. Or
• Add unique constraint to column (UnitCode) of Enrolment table if you only need the reference on UnitCode column.

Hth.