Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Why is it still possible to insert a foreign key that doesn't exist?

mysql>  create table products(id integer unsigned auto_increment primary key);
Query OK, 0 rows affected (0.05 sec)

mysql> CREATE TABLE orders (
    ->     id integer PRIMARY KEY auto_increment,
    ->     product_id integer REFERENCES products (id),
    ->     quantity integer,
    ->     INDEX product_id_idx (product_id)
    -> );
Query OK, 0 rows affected (0.05 sec)
mysql> insert into orders(product_id,quantity) value(1,1);
Query OK, 1 row affected (0.00 sec)

Since product 1 doesn't exist,the insert statement is supposed to fail,but in fact not.

Why?

like image 570
user198729 Avatar asked Feb 27 '23 12:02

user198729


2 Answers

You should explicitly define the foreign key below the column definitions.

You should also make product_id unsigned since the parent key is unsigned:

CREATE TABLE orders (
  id integer PRIMARY KEY auto_increment,
  product_id integer unsigned,
  quantity integer,
  INDEX product_id_idx (product_id),
  CONSTRAINT FK_ORDER_TO_PRODUCT FOREIGN KEY (product_id) REFERENCES products (id)
 ) engine=innodb;
like image 191
Ike Walker Avatar answered Mar 02 '23 00:03

Ike Walker


Foreign keys are only supported when MySQL uses the InnoDB storage engine.

MySQL uses MyISAM as the default storage engine, where foreign key constraints and are simply ignored.

Try your example with the following tables:

CREATE TABLE products (
     id integer unsigned auto_increment primary key
) ENGINE=INNODB;

CREATE TABLE orders (
     id integer PRIMARY KEY auto_increment,
     product_id integer unsigned,
     quantity integer,
     INDEX product_id_idx (product_id),
     FOREIGN KEY (product_id) REFERENCES products (id)
) ENGINE=INNODB;

Also make sure that the referencing and referenced columns are of the exact same datatype.

like image 43
Daniel Vassallo Avatar answered Mar 02 '23 00:03

Daniel Vassallo