MySQL on delete cascade. Test Example

Question

I am wondering about this test question. I prepared the example myself and tested it but I still feel unsure of the answer.

With the following:

CREATE TABLE foo (
  id INT PRIMARY KEY AUTO_INCREMENT, 
  name INT
)

CREATE TABLE foo2 (
  id INT PRIMARY KEY AUTO_INCREMENT, 
  foo_id INT REFERENCES foo(id) ON DELETE CASCADE
)

As far as I can see the answer is:

a. Two tables are created

Although there are also:

b. If a row in table foo2, with a foo_id of 2 is deleted, then the row with id=2 in the table foo is automatically deleted

d.If a row with id = 2 in table foo is deleted, all rows with foo_id = 2 in table foo2 are deleted

In my example I would have used the delete syntax:

DELETE FROM foo WHERE id = 2;
DELETE FROM foo2 WHERE foo_id = 2;

For some reason I was unable to find any relationship between the tables although it seems like there should be one. Maybe there is some MySQL setting or perhaps is ON DELETE CASCADE not used properly in the table creation queries? I am left wondering...

Answer 1

Answer d. is correct, if and only if the storage engine actually supports and enforces foreign key constraints.

If the tables are created with Engine=MyISAM, then neither b. or d. is correct.

If the tables are created with Engine=InnoDB, then d. is correct.

NOTE:

This is true for InnoDB if and only if FOREIGN_KEY_CHECKS = 1; if FOREIGN_KEY_CHECKS = 0, then a DELETE from the parent table (foo) will not remove rows from the child table (foo2) that reference a row removed from the parent table.

Verify this with the output from SHOW VARIABLES LIKE 'foreign_key_checks' (1=ON, 0=OFF) (The normal default is for this to be ON.)

The output from SHOW CREATE TABLE foo will show which engine the table uses.

The output from SHOW VARIABLES LIKE 'storage_engine' will show the default engine used when a table is created and the engine is not specified.