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.
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'
You have to remove these found constraints first before DROP TABLE
.