CODEWITHSUNDEEP
mysql
Ricardo Souza
Ricardo Souza

Reputation: 16456

Foreign keys on create table doesn't get created

Executing my code didn't cause errors and didn't create the expected keys on this server:

create table table1 (
    id int not null auto_increment primary key,
    name varchar(10) not null default ''   
) engine=innodb;

create table table2 (
    id int not null auto_increment primary key,
    idTable1 int null references table1(id) on delete cascade
) engine=innodb;

I've seen some declaring the constraints as:

columnName int not null, foreign key (columnName) references table1(id) on delete cascade

but I think I've already used the previous syntax before and it worked on a different server. I just can't remember for sure.

Have you seen this before? Am I wrong in assuming my syntax would work as it works on MSSQL? If so, why this doesn't cause an error to be thrown?

Edit.:

InnoDB is enabled and set to default with SET storage_engine=INNODB; before running the queries to make sure.

Keys added with the second syntax example work. Keys added with alter table work also.

Upvotes: 0

Views: 415

Answers (1)

avisheks
avisheks

Reputation: 1180

MySQL doesn't support column level Foreign key constraint addition to the CREATE syntax. It has to be table level.

Furthermore, MySQL does not recognize or support “inline REFERENCES specifications” (as defined in the SQL standard) where the references are defined as part of the column specification. MySQL accepts REFERENCES clauses only when specified as part of a separate FOREIGN KEY specification. For storage engines that do not support foreign keys (such as MyISAM), MySQL Server parses and ignores foreign key specifications.

Further reading: here

Upvotes: 5

Related Questions