CODEWITHSUNDEEP
mysql
digdigdoot
digdigdoot

Reputation: 761

foreign key incorrectly formed:MySQL

My Script

create table student
(
stud_id int(9) unsigned not null,
stud_name varchar(30),
stud_phone int(10),
stud_dob date,
stud_city varchar(15),
stud_address varchar(50),
stud_postcode int(5),
primary key(stud_id)
);

create table subject
(
sub_code varchar(9) not null,
sub_title varchar(30),
primary key(sub_code)
);

create table grade
(
stud_id int(9) unsigned not null,
sub_code varchar(9) not null,
sem int(1) not null,
year int(4) not null,
comment varchar(50),
primary key(stud_id,sub_code,sem,year),
foreign key(stud_id) references student,
foreign key(sub_code) references subject
);

I'm not sure why it isn't referencing, I'm quite new to SQL. The column data types are the same, the collation is both latin1, and the signing definition is the same, what is exactly wrong? Any help is appreciated.

Upvotes: 2

Views: 46

Answers (2)

Yann G
Yann G

Reputation: 104

You also need to specify the column of the table you are referencing. This is how you write it, in your case:

create table grade
(
[...]
foreign key(stud_id) references student (stud_id),
foreign key(sub_code) references subject (sub_code)
);

MySql Doc on foreign keys

Upvotes: 1

Sven Hakvoort
Sven Hakvoort

Reputation: 3621

You also need to specify which column in the other table is referenced, thus the foreign key declaration needs to be foreign key(stud_id) references student(stud_id) for example.

So for example:

create table subject
(
sub_code varchar(9) not null,
sub_title varchar(30),
primary key(sub_code)
);

create table grade
(
stud_id int(9) unsigned not null,
sub_code varchar(9) not null,
sem int(1) not null,
year int(4) not null,
comment varchar(50),
primary key(stud_id,sub_code,sem,year),
foreign key(sub_code) references subject(sub_code)
);

Upvotes: 3

Related Questions