CODEWITHSUNDEEP
mysqldatabaseforeign-keysforeign-key-relationship
Genocide_Hoax
Genocide_Hoax

Reputation: 853

MYSQL doesn't check for Foreign key exsistence

I am facing a strange problem here.

TABLE 1:

create table degree (degree_id varchar(6) primary key , degree_name varchar(32) unique key , degree_abbr varchar(3));

TABLE 2:

create table course (course_id varchar(6) primary key , degree_id varchar(6) not null, course_name varchar(40) not null ,  foreign key (degree_id) references degree 
(degree_id));

Now as far as I understand SQL , I cannot insert any value in the course table if the value of field degree_id doesn't exist in the degree table.

But if I try to insert something like 

insert into course values('cor_001' , 'blah' , 'COURSE NAME' );

This query runs successfully , even though 'blah' is not a valid degree_id.

Upvotes: 1

Views: 64

Answers (1)

Marc B
Marc B

Reputation: 360702

You're probably using MyISAM tables, which do NOT support foreign keys. The FK directives are parsed and accepted, then ignored.

You need to use InnoDB tables for proper FK support:

CREATE TABLE (...) ENGINE=InnoDB;
                  ^^^^^^^^^^^^^^

If you do a show create table your_table, you'll see the engine type being used at the end of the output.

Upvotes: 3

Related Questions