Creating foreign keys in MySQL

Question

I'm trying to link two tables together via a foreign key in MySQL. The CLIENTS table should be linked to another table (BIDS) with the Client ID attribute.

CREATE TABLE CLIENTS (
 CLIENTID            NUMERIC(3) NOT NULL,
 FOREIGN KEY(CLIENTID) REFERENCES BIDS(CLIENTID),
 PRIMARY KEY(CLIENTID, EMAILADDRESSES,PHONENUMBERS,CONTACTS)
 );

However, MySQL returns this error when I try to execute the code.

#1005 - Can't create table 'CLIENTS' (errno: 150)

It doesn't seem to be a syntax error, so does anyone know what's causing the issue or how can I fix it?

possible duplicate of [MySQL Creating tables with Foreign Keys giving errno: 150](http://stackoverflow.com/questions/1457305/mysql-creating-tables-with-foreign-keys-giving-errno-150) — Marcus Adams, Nov 20 '14 at 18:44
Your example looks incomplete to me: how can you put columns `EMAILADDRESSES`, `PHONENUMBERS`, `CONTACTS` into a primary key if you do not have these columns in the table? — peschü, Nov 20 '14 at 18:47
What is the data type for BIDS(CLIENTID)? The most common reason for errno 150 is that the data type of the FK does not match the data type of the PK that it references. — Bill Karwin, Nov 20 '14 at 19:15
Find Answer on this link as i updated. https://stackoverflow.com/a/46240799/8158782 — Atishay, Sep 15 '17 at 14:01

Jay Blanchard · Accepted Answer · 2014-11-20T22:38:18.220

When creating a table with a primary key using multiple columns they have to be specified in the query -

CREATE TABLE CLIENTS (
 CLIENTID NUMERIC(3) NOT NULL,
 EMAILADDRESSES CHAR(64),
 PHONENUMBERS VARCHAR(16),
 CONTACTS VARCHAR(32), 
 FOREIGN KEY(CLIENTID) REFERENCES BIDS(CLIENTID),
 CONSTRAINT key_name PRIMARY KEY(CLIENTID, EMAILADDRESSES,PHONENUMBERS,CONTACTS)
);

You also must specify a name for a multiple column primary key. Why you would want all of those columns as a key is a mystery though.

Creating foreign keys in MySQL

1 Answers1