0

I have two tables

CREATE TABLE `category` (`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8
CREATE TABLE `item` (`id` int(10) unsigned NOT NULL AUTO_INCREMENT, 
    categoryid` int(10) unsigned DEFAULT NULL, 
    PRIMARY KEY (`id`), KEY `fk_categoryid_item` (`categoryid`), 
    CONSTRAINT `fk_categoryid_item` FOREIGN KEY (`categoryid`) 
    REFERENCES `category` (`id`) ON DELETE CASCADE) 
    ENGINE=InnoDB DEFAULT CHARSET=utf8

In the table category I have a record with id 2. In the item I have a record with id = 1, categoryid = 2, with 2 as the foreign key referring to the category table. If I delete the row in the category table with the id 2, the record in the item table that has the categoryid as 2 also gets deleted. This is as expected because of on delete cascade. But If I try to drop the table category, I get the error Error Code: 

1217. Cannot delete or update a parent row: a foreign key constraint fails

Why does this happen ? Of course, setting foreign_key_checks = 0 dropping the table becomes possible. But I would like to know why does this happen that we can delete the records, but can not drop the table with on cascade delete option. Does this option only apply for deleting records, but not for dropping tables.

I checked the documentation, I could not find any explanation for this.

Please let me know if there is something fundamental that I am missing or if you point out to the related documentation it would be helpful. I am using MySQL 5.7.

Thanks in advance.

Aravamudhan
  • 198
  • 2
  • 11
  • Possible duplicate of [Can't drop table: A foreign key constraint fails](http://stackoverflow.com/questions/11100911/cant-drop-table-a-foreign-key-constraint-fails) – Rupsingh Aug 10 '16 at 07:06
  • A logic explanation could be that if you drop the parent table, you won't be able to insert/update any row in the child one anymore, because when you insert or update records it must check the FK constraints – the_nuts Aug 10 '16 at 07:36
  • 1
    You are missing something fundamental. MySQL is a **relational** database. Foreign keys are used to set up relations. If you delete a piece that depends on the relation, relation is broken and you set that relation up in order to ensure data **integrity**. Dropping a table that you relate to violates integrity, hence you get the error. It's normal. Having no records isn't the same as having no table. – Mjh Aug 10 '16 at 07:55

1 Answers1

0

If you delete the table category but do not remove/alter the foreign key, then that will be left pointing to nothing. Internally the database has a management system that reinforces the referential constraints and that prevents you from creating lose ends. See also this, this and this questions.

It has something to do also with the math behind it, it is called relational algebra. I am not at that level either, but I think it breaks the definition of a FK if you delete one of the associated tables.

In database relational modeling and implementation, a unique key is a set of zero or more attributes, the value(s) of which are guaranteed to be unique for each tuple (row) in a relation.

Elzo Valugi
  • 27,240
  • 15
  • 95
  • 114
  • Then why does 'on delete set null' also prevents the parent table to be from deleted ? Should not that make all the records pointing to the parent set to null ? Is there some design decision of the dbms behind this ? – Aravamudhan Aug 10 '16 at 07:26
  • From my understanding a FK is implemented at row level, but it creates a constraint at table level. – Elzo Valugi Aug 10 '16 at 07:33
  • `on delete set null` - is still a fk, it just chooses to solve the delete trigger with another action - setting NULL on dependent values instead of deleting the whole entry – Elzo Valugi Aug 10 '16 at 07:44
  • How could it set it to null if you drop the table? More generally, it could take no action on the parent table if you drop that table. So, you cannot drop it. – Federico Razzoli Aug 10 '16 at 10:30