0

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');
  • Please provide a PRIMARY KEY for each table. – Rick James Jul 20 '18 at 18:33
  • PKs are customer.CUS_CODE and invoice.INV_NUMBER. – Keith Jul 21 '18 at 3:00
2

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)

  • Thank you! Can't believe I made such a silly mistake haha. – Keith Jul 21 '18 at 10:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.