I'm using MySQL with InnoDB engine. I create a new table that contains a foreign key, eg:
CREATE TABLE rooms ( id INTEGER NOT NULL AUTO_INCREMENT, my_id VARCHAR(15), house_id INTEGER, PRIMARY KEY (id), FOREIGN KEY(house_id) REFERENCES houses (id) ON DELETE CASCADE )
When I use a DB tool such as DBeaver to see the details of my database and this table, I see that my foreign key
house_id is missing the
ON DELETE CASCADE setting.
DBeaver also shows the DDL for this table as follows:
CREATE TABLE `rooms` ( `id` int(11) NOT NULL AUTO_INCREMENT, `my_id` varchar(15) COLLATE utf8mb4_general_ci DEFAULT NULL, `house_id` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `house_id` (`house_id`), CONSTRAINT `rooms_ibfk_1` FOREIGN KEY (`house_id`) REFERENCES `houses` (`id`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
Why does the DDL have the extra line
KEY 'house_id' ('house_id').? This extra line also causes my foreign key to show 2
house_id columns in my DBeaver table viewer. If I get rid of this, my
ON DELETE CASCADE gets set correctly.
I'm using an ORM so I don't have control of the
CREATE TABLE statement, so I'm just trying to at least understand what's going on here.