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:
Correct syntax:
customer.CUS_CODE
is treated asdbname.tablename
. What is needed istablename(columnname)
ordbname.tablename(columnname)