Creating foreign keys in phpMyAdmin (MySQL)

Question

I'm trying to create a database in MySQL on phpMyAdmin. I am able to create the tables without any trouble, but I also want to add some foreign keys. In this case I want to link the BIDS and CLIENTS tables via the CLIENTID attribute.

CREATE TABLE BIDS (
 BIDID               NUMERIC(3) NOT NULL PRIMARY KEY,
 CLIENTID            NUMERIC(3) NOT NULL
 );

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

Research has told me that the syntax is correct, but this code returns the following error.

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

Apparently, a solution might be involved with something called 'InnoDB'. How can I use it to fix my problem?

Answer 1

Syntax is fine but problem is with FORIEGN KEY statement as below. You can't create FK on a non-key column. In BIDS table it's BIDID which is defined as Primary Key and not CLIENTID

FOREIGN KEY (CLIENTID) REFERENCES BIDS (CLIENTID)

So, your FORIEGN KEY definition should actually be

FOREIGN KEY (CLIENTID) REFERENCES BIDS (BIDID)

See a demo here http://sqlfiddle.com/#!2/f1c9ec