CODEWITHSUNDEEP
mysqlsql
JeraldPunx
JeraldPunx

Reputation: 319

cannot add foreign key constraint

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`)
)

Upvotes: 0

Views: 250

Answers (3)

Siddhant
Siddhant

Reputation: 196

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

Upvotes: 0

DB_learner
DB_learner

Reputation: 1026

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

Upvotes: 1

Naveen Kumar Alone
Naveen Kumar Alone

Reputation: 7678

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)
);

Upvotes: 2

Related Questions