mysql foreign key concept

Question

CREATE TABLE parent (id INT NOT NULL,
                     PRIMARY KEY (id)
) ENGINE=INNODB;


CREATE TABLE child (id INT, parent_id INT,
                    INDEX par_ind (parent_id),
                    FOREIGN KEY (parent_id) REFERENCES parent(id)
                      ON DELETE CASCADE
) ENGINE=INNODB;

I dont understand the meaning of putting ENGINE = INNODB here, and why do we use ON DELETE CASCADE?

Answer 1

MySQL is the DB engine. It can use multiple storage engines. MyISAM is the default storage engine for MySQL and it does not support foreign keys. InnoDB is another storage engine that does support foreign keys. You must specify ENGINE=InnoDB because MySQL will use MyISAM by default.

ON DELETE CASCADE will delete all rows in a table that have a foreign key that references a key that is deleted. I think it is dangerous and defeats a lot of the purpose of foreign key restriction, so I would avoid using it, but this is just my personal opinion.

Say you have:

+-------+-------+
| ordID | proID |
+-------+-------+
|     1 |     1 | 
|     2 |     1 | 
|     3 |     1 | 
|     4 |     2 | 
|     5 |     2 | 
+-------+-------+

And on OrdersItems it has FOREIGN KEY (proID) REFERENCES Products (proID) ON DELETE CASCADE.

Then if someone runs

DELETE FROM Products WHERE proID = 2

Then rows with ordID 4 and 5 will also be deleted (it cascades).

Answer 2

engine=innodb will ensure you get foreign key support. The default MyISAM engine doesn't support foreign keys. On delete cascade will remove the child row if the referenced row in the parent table is removed.