CODEWITHSUNDEEP
mysql
Hayde
Hayde

Reputation: 53

Mysql Cannot add foreign key constraint for my tables

Hey guys getting an Error code 1215, SQL state HY000: Cannot add foreign key constraint for my PERSON_GROUP table and IMAGES table.. Don't know why, is there something wrong with my referencing? I've tried rewriting it but its just not working...

Updated code im now just getting an error for FOREIGN KEY (ID) REFERENCES INSTRUMENT(ID)

Upvotes: 0

Views: 683

Answers (2)

Tunde Michael
Tunde Michael

Reputation: 364

I had this problem. The problem was database engine. Until i added the ENGINE=MyISAM DEFAULT CHARSET=latin1 before it worked. I guess my default was innoDB or something else.

CREATE TABLE LECTURE_NOTE (
  ID bigint(20) NOT NULL, 
  NOTE VARCHAR(1000) NOT NULL, 
  NOTE_DATE TIMESTAMP NULL DEFAULT NULL,
  LECTURE_ID bigint(20) NOT NULL,
  PUBLISHER_ID bigint(20) NOT NULL,
  PRIMARY KEY (ID),
  CONSTRAINT fk_lecture_id FOREIGN KEY (LECTURE_ID) REFERENCES 
  COURSE_LECTURE (ID),
  CONSTRAINT fk_publisher_id FOREIGN KEY (PUBLISHER_ID) REFERENCES 
  PUBLISHER (ID)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Upvotes: 0

Vulcronos
Vulcronos

Reputation: 3456

You can only create foreign keys that reference either the primary key or a unique key. Since ID is the primary key of PERSON change your foreign keys to point to ID instead of email. You could also make email a unique column, which is probably a good idea to make sure no one reuse the same email address, but it is still less storage to make foreign keys on an integer then a string.

Upvotes: 3

Related Questions