cannot add foreign key constraint

Question

ERROR MESSAGE: #1215 - Cannot add foreign key constraint

hello i can't create table foreign key... how to do this... i have two table.. the first table got two primary key then the 2nd table is 1 primary key... the first table is courseid varchar(5) and courseyear int and the second is subj_id varchar(5)

create table studentload(
student_id varchar(11) not null,
courseid varchar(5) not null,
courseyear int not null,
subj_id varchar(5) not null,
foreign key (courseid,courseyear) references course(courseid,courseyear),
foreign key (subj_id) references subject(subj_id)
)

EDIT

this is all table that I inserted already

CREATE TABLE IF NOT EXISTS `course` (
  `courseid` varchar(5) NOT NULL,
  `courseyear` int(11) NOT NULL,
  `coursedesc` varchar(50),
  `subj_id` varchar(5) NOT NULL,
  PRIMARY KEY (`courseid`,`courseyear`)
) 


CREATE TABLE IF NOT EXISTS `subject` (
  `subj_id` varchar(5) NOT NULL,
  `subj_name` varchar(50) NOT NULL,
  `courseid` varchar(5),
  `courseyear` int(11),
  foreign key (`courseid`,`courseyear`) references `courseid` (`courseid`,`courseyear`)
)

What happens when you try to add the constraint? How exactly are you trying to add the constraint? What happens when you do? Do you get an error message? What is the error message? Please edit your question to update it with this information, cut & pasted from the screen, not paraphrased, so that we can help you. — Andy Lester, Sep 06 '13 at 05:12
And that's it, nothing more to that error message? Also, do you think that information might have been relevant to your question? — Phil, Sep 06 '13 at 05:13
Possible duplicate of http://stackoverflow.com/questions/16969060/mysql-error-1215-cannot-add-foreign-key-constraint — Phil, Sep 06 '13 at 05:28
view my answer posted answer http://stackoverflow.com/questions/18650302/cannot-add-foreign-key-constraint/18650510#18650510 and fiddle http://sqlfiddle.com/#!2/e850e — Naveen Kumar Alone, Sep 06 '13 at 05:33

score 2 · Accepted Answer · edited Jan 02 '16 at 03:24

Here it is sample SQLFiddle

You have mandatory to add primary key (subj_id) in your studentload table

In your foriegn relationship courseid is not tablename. It should be

"references course(courseid,courseyear)"

Like

CREATE TABLE IF NOT EXISTS `course` (
  `courseid` varchar(5) NOT NULL,
  `courseyear` int(11) NOT NULL,
  `coursedesc` varchar(50),
  `subj_id` varchar(5) NOT NULL,
  PRIMARY KEY (`courseid`,`courseyear`)
); 


CREATE TABLE IF NOT EXISTS `subject` (
  `subj_id` varchar(5) NOT NULL,
  `subj_name` varchar(50) NOT NULL,
  `courseid` varchar(5),
  `courseyear` int(11),
  foreign key (`courseid`,`courseyear`) references `course` (`courseid`,`courseyear`),
  primary key (`subj_id`)
);

create table studentload(
  student_id varchar(11) not null,
  courseid varchar(5) not null,
  courseyear int not null,
  subj_id varchar(5) not null,
  foreign key (courseid,courseyear) references course(courseid,courseyear),
  foreign key (subj_id) references subject(subj_id)
);

score 1 · Answer 2 · edited Jan 02 '16 at 03:24

1

You have used "references courseid(courseid,courseyear) ". courseid is not your tablename. It should be "references course(courseid,courseyear) "

edited Jan 02 '16 at 03:24

Josh Crozier

233,099
56
391
304

answered Sep 06 '13 at 05:28

DB_learner

1,026
9
15

score 0 · Answer 3 · answered Jun 13 '20 at 18:27

0

Sometime it is due to the order of the table creations. Make sure you create the non-foreign key tables first.

answered Jun 13 '20 at 18:27

Siddhant

196
2
9

cannot add foreign key constraint

3 Answers3