0

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.

HellDryx
  • 5
  • 1
  • seems to me that you are referring to the core table .. (same table you are delete with a delete cascade) .. could be your constraisn si for anothers table?? – ScaisEdge Nov 22 '19 at 09:31
  • Creating and immediately dropping this table works fine for me which suggest this table is a referenced table somewhere else in your db, This may help you find it https://dba.stackexchange.com/questions/102371/mysql-how-to-check-foreign-keys-related-to-a-table – P.Salmon Nov 22 '19 at 09:43
  • Thank you @P.Salmon ! I indeed had other tables referring to the `Core` one that didn't have `ON DELETE CASCADE` on their foreign keys. – HellDryx Nov 22 '19 at 09:52

1 Answers1

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.

Sebastian Brosch
  • 42,106
  • 15
  • 72
  • 87