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

Question

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?

Answer 1

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.

Answer 2

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;