Search code examples
phpmysqlforeign-keyscreate-table

Creating foreign keys in MySQL

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?

Solution

  • 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.