MySQL – Generated DDL Doesn’t Match CREATE TABLE Statement

innodbMySQLpythonsqlalchemy

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.

Thanks,

Best Answer

As the manual says (emphasis mine):

MySQL requires indexes on foreign keys and referenced keys so that foreign key checks can be fast and not require a table scan. In the referencing table, there must be an index where the foreign key columns are listed as the first columns in the same order. Such an index is created on the referencing table automatically if it does not exist.

In other words, this is an expected, documented behaviour. If DBeaver cannot see a difference between table constraints and its indexes, may be choose some other tool.

Related Question