mysql- cannot add foreign key constraint

Question

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

Gordon Linoff · Accepted Answer · 2015-09-06 15:07:59Z

1

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.

answered Sep 6, 2015 at 15:07

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

SouvikMaji Over a year ago

in which rdbms, the sql statements would have worked?

Gordon Linoff Over a year ago

I don't think any database would take the foreign key like that. Most databases support check constraints both syntactically and enforce them.

Collectives™ on Stack Overflow

mysql- cannot add foreign key constraint

1215 - Cannot add foreign key constraint

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1215 - Cannot add foreign key constraint

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related