mysql foreign key to compound primary key

Question

I try to achive the following using MySQL Server 5.x.

I have a table named Customer created like this:

CREATE TABLE Customer(
Title VARCHAR(30) NOT NULL, 
Name VARCHAR(100) NOT NULL, 
FirstName VARCHAR(100) NOT NULL, 
Street VARCHAR(300) NOT NULL, 
HouseNumber VARCHAR(30) NOT NULL, 
ZipCode VARCHAR(30) NOT NULL, 
City VARCHAR(100) NOT NULL, 
Telephone VARCHAR(30) NOT NULL, 
EMail VARCHAR(300) NULL, 
CONSTRAINT PK_Customer PRIMARY KEY(Title,Name,FirstName),
INDEX Index_Name(Name));

And a second table Named 'Order' created like this:

CREATE TABLE `Order`(
Number BIGINT NOT NULL AUTO_INCREMENT,
Customer VARCHAR(230) NOT NULL,
Issued DATETIME NOT NULL,
PRIMARY KEY(Number),
CONSTRAINT FK_Customer FOREIGN KEY(Customer) 
REFERENCES Customer(PK_Customer));

But I get an error with the number:

ERROR 1005 (HY000): Can't create table 'SBZ.Order' (errno: 150)

The innodb engine status shows me this:

------------------------
LATEST FOREIGN KEY ERROR
------------------------
160317 16:05:29 Error in foreign key constraint of table SBZ/Order:
FOREIGN KEY(Customer) REFERENCES Customer(PK_Customer)):
Cannot resolve column name close to:
))

Is it possible to create a foreign key to a compound primary key using constraint?

Any help appriciated. :)

score 1 · Answer 1 · answered Mar 17 '16 at 15:18

First, you should have a CustomerId column in the first table. It should be auto-incremented. So the right definitions are:

CREATE TABLE Customer (
    CustomerId int auto_increment primary key,
    . . .
    unique (title, firstname, name)
);

Then you can create a correct foreign key relationship to CustomerId:

CustomerId int,
. . .
CONSTRAINT FK_Customer FOREIGN KEY(CustomerId) REFERENCES Customer(CustomerId)

This is "right" because such synthetic keys have several advantages:

Foreign key references are much simpler.
You can change the components easily (changing part of a foreign key requires understanding cascading constraints).
Integers in indexes are more efficient than strings.

Of course, you can do the same with a composite primary key. You just need all three columns in the second table:

Title VARCHAR(30), 
Name VARCHAR(100), 
FirstName VARCHAR(100), 
CONSTRAINT FK_Customer FOREIGN KEY(Title, Name, Firstname) REFERENCES Customer(Title, Name, Firstname)

Your column `Customer.CustomerId` doesn't *have* to be autoincrement, but it does have to be unique and autoincrement is a really handy way to do that. — Darwin von Corax, Mar 17 '16 at 15:22

score 0 · Accepted Answer · answered Mar 17 '16 at 15:16

Your child table's foreign key must contain the same columns, in the same order, as the primary key of the parent table. In your case it would look like

CREATE TABLE `Order`(
  Number BIGINT NOT NULL AUTO_INCREMENT,
  Title VARCHAR(30) NOT NULL, 
  Name VARCHAR(100) NOT NULL, 
  FirstName VARCHAR(100) NOT NULL,
  ...
  CONSTRAINT FK_Customer FOREIGN KEY (Title, Name, FirstName)
    REFERENCES Customer (Title, Name, FirstName)
);

Note the columns don't have to be in the same order in the table, just in the constraint's column list.

mysql foreign key to compound primary key

2 Answers2