Error Code: 1215: Cannot add foreign key constraint
Greetings,
I am a student learning SQL so I apologize in advance for the naive questions.
I am trying to follow along with my text and entered in the tables:
CREATE TABLE PAINTER (
PTR_NUM CHAR(4) NOT NULL UNIQUE,
PTR_LASTNAME CHAR(15) NOT NULL,
PTR_FIRSTNAME CHAR(15) NOT NULL,
PTR_AREACODE CHAR(3),
PTR_PHONE CHAR(8),
PRIMARY KEY (PTR_NUM));
CREATE TABLE GALLERY (
GAL_NUM CHAR(4) NOT NULL UNIQUE,
GAL_OWNER CHAR(35),
GAL_AREACODE CHAR(3) NOT NULL,
GAL_PHONE CHAR(8) NOT NULL,
GAL_RATE Numeric(4,2),
PRIMARY KEY (GAL_NUM));
CREATE TABLE PAINTING (
PTNG_NUM CHAR(4) NOT NULL UNIQUE,
PTNG_TITLE CHAR(35),
PTNG_PRICE Numeric(9,2),
PTR_NUM CHAR(4) NOT NULL,
GAL_NUM CHAR(4),
PRIMARY KEY(PTNG_NUM),
FOREIGN KEY (PTR_NUM) REFERENCES PAINTER
ON UPDATE CASCADE,
FOREIGN KEY (GAL_NUM) REFERENCES GALLERY
ON UPDATE CASCADE);
On the last table - I keep getting the 1215 error code. I made sure the Engine was default InnoDB, I have checked the syntax, the type are all the same (Char) - I have starred at several forums, documentation, and posts for hours and I am just not experienced enough to see what I am doing wrong. Did not help that the text book also transposed the Primary Key name which really confused me for a moment!
Also - I looked for an error code section in this forum and I seem to be on the same page as others who have had this error? You help would be greatly appreciated.
Thanks,
Jason