MySQL : error 1217 on DROP TABLE. Foreign key refering on its own primary key. ON DELETE CASCADE is on

Question

I'm trying this query

DROP TABLE IF EXISTS Core;

on a table created with this query

CREATE TABLE Core (
    id serial PRIMARY KEY,

    title varchar(512),
    kind ENUM('User', 'Organisation', 'Channel', 'Collection', 'Text', 'Picture', 'Sound', 'Video', 'UserGroup'),
    is_activated BOOLEAN DEFAULT true,
    date_created DATETIME DEFAULT CURRENT_TIMESTAMP,
    date_updated DATETIME,
    date_augmented DATETIME,

    author_core BIGINT UNSIGNED NOT NULL,
    FOREIGN KEY (author_core) REFERENCES Core(id) ON DELETE CASCADE
)   

but i get the error #1217 - A foreign key constraint fails.

I know the dirty workaround that is disabling the foreign key check with SET foreign_key_checks = 0; but i'm still wondering why the ON DELETE CASCADE of the field author_core is not doing its job.

Answer 1

It looks like there is another table refering to the Core table. You can get all refering keys of the other tables with following query:

SELECT TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
WHERE REFERENCED_TABLE_NAME = 'Core' AND TABLE_NAME <> 'Core'

demo on dbfiddle.uk

You have to remove these found constraints first before DROP TABLE.