why FK constraint does not work in MySql

Question

I have created two tables ORDERS and ORDERITEMS with the following constraint:

alter table OrderItems add constraint FK_Reference_30 foreign key (orderId)
  references Orders (orderId) on delete restrict on update restrict;

If I want delete one entry in ORDERS table and that orderId is used in ORDERITEMS table, I should get error or warning, I think. But actually I got nothing. I inserted two rows in ORDERS and few rows in ORDERITEMS. When I tried to delete all rows in ORDERS, I did it. No complaint. I am using MySQL database with Toad for MySQL.

Answer 1

What database engine are you using? InnoDB is the only one that supports foreign key constraints.

Answer 2

I would guess that you are using MyISAM tables.

If you run "SHOW CREATE TABLE OrderItems" you will see that it failed to record the foreign key definition.

MyISAM tables do not support foreign keys. MySQL parses the syntax, but then silently discards it when you use MyISAM. If you use InnoDB tables for both Orders and OrderItems, this will work better.

---

You should have InnoDB enabled by default. It would be unusual for it to be disabled. You can verify that you do this way:

mysql> show variables like 'have_innodb';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| have_innodb   | YES   | 
+---------------+-------+

If that's true, then you can specify ENGINE=INNODB when you CREATE or ALTER a table:

mysql> ALTER TABLE Orders ENGINE=INNODB;
mysql> ALTER TABLE OrderItems ENGINE=INNODB;

Be sure to double-check that it succeeded, with SHOW CREATE TABLE <name>.

Now you can add the constraint, and it should take effect.