CODEWITHSUNDEEP
mysqlsqldatabaseforeign-keys
SouvikMaji
SouvikMaji

Reputation: 1145

mysql- cannot add foreign key constraint

When I am trying to add foreign key constraint to the course table mysql said:

1215 - Cannot add foreign key constraint

create table course
(course_id      varchar(8), 
 title          varchar(50), 
 dept_name      varchar(20),
 credits        numeric(2,0) check (credits > 0),
 primary key (course_id),
 foreign key (dept_name) references department
    on delete set null
);

The ddl statement I used to create the table department is:

create table department
(dept_name      varchar(20), 
 building       varchar(15), 
 budget             numeric(12,2) check (budget > 0),
 primary key (dept_name)
);

I have used set foreign_key_checks=0 before creating any table. The LATEST FOREIGN KEY ERROR says:

Syntax error close to:

on delete set null

Upvotes: 0

Views: 824

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269447

Try including the column name in the foreign key definition:

foreign key (dept_name) references department(dept_name)

Here is a SQL Fiddle.

Note that the check constraint is parsed, but not enforced. MySQL accepts the syntax but does nothing.

Upvotes: 1

Related Questions