MySQL – Fix Error 1239: Incorrect Foreign Key Definition

database-designforeign keyMySQL

While creating primary keys and foreign keys for my tables I got the error 1239 and apparently I'm the only one having this error as I could not find anything with a Google search. I was wondering if anyone can tell me what went wrong?

The foreign key error happened specifically when I was trying to link invoice.CUS_CODE to customer.CUS_CODE with:

alter table invoice add constraint CUS_CODE
    foreign key (CUS_CODE) references customer.CUS_CODE

I also tried disabling foreign_key_checks but no luck so far.

Here's the tables, and some sample data:

CREATE TABLE CUSTOMER(
CUS_CODE int,
CUS_LNAME varchar(15),
CUS_FNAME varchar(15),
CUS_INITIAL varchar(1),
CUS_AREACODE varchar(3),
CUS_PHONE varchar(8),
CUS_BALANCE float(8)
);
INSERT INTO CUSTOMER VALUES('10010','Ramas','Alfred','A','615','844- 
2573','0');
INSERT INTO CUSTOMER VALUES('10011','Dunne','Leona','K','713','894- 
1238','0');
INSERT INTO CUSTOMER VALUES('10012','Smith','Kathy','W','615','894- 
2285','345.86');
INSERT INTO CUSTOMER VALUES('10013','Olowski','Paul','F','615','894- 
2180','536.75');
INSERT INTO CUSTOMER VALUES('10014','Orlando','Myron','','615','222- 
1672','0');
INSERT INTO CUSTOMER VALUES('10015','O''Brian','Amy','B','713','442- 
3381','0');
INSERT INTO CUSTOMER VALUES('10016','Brown','James','G','615','297- 
1228','221.19');
INSERT INTO CUSTOMER VALUES('10017','Williams','George','','615','290- 
2556','768.93');
INSERT INTO CUSTOMER VALUES('10018','Farriss','Anne','G','713','382- 
7185','216.55');
INSERT INTO CUSTOMER VALUES('10019','Smith','Olette','K','615','297- 
3809','0');

CREATE TABLE INVOICE (
INV_NUMBER int,
CUS_CODE int,
INV_DATE date,
INV_SUBTOTAL float(8),
INV_TAX float(8),
INV_TOTAL float(8)
);
INSERT INTO INVOICE VALUES('1001','10014','2016-01- 
16','24.90','1.99','26.89');
INSERT INTO INVOICE VALUES('1002','10011','2016-01- 
16','9.98','0.80','10.78');
INSERT INTO INVOICE VALUES('1003','10012','2016-01- 
16','153.85','12.31','166.16');
INSERT INTO INVOICE VALUES('1004','10011','2017-01- 
16','34.97','2.80','37.77');
INSERT INTO INVOICE VALUES('1005','10018','2017-01- 
16','70.44','5.64','76.08');
INSERT INTO INVOICE VALUES('1006','10014','2017-01- 
16','397.83','31.83','429.66');
INSERT INTO INVOICE VALUES('1007','10015','2017-01- 
16','34.97','2.80','37.77');
INSERT INTO INVOICE VALUES('1008','10011','2017-01- 
16','399.15','31.93','431.08');

Best Answer

Syntax error:

alter table invoice add constraint CUS_CODE
    foreign key (CUS_CODE) references customer.CUS_CODE;

Correct syntax:

alter table INVOICE add constraint CUS_CODE
    foreign key (CUS_CODE) references CUSTOMER(CUS_CODE);

customer.CUS_CODE is treated as dbname.tablename. What is needed is tablename(columnname) or dbname.tablename(columnname)