Hello All,

Referential Integrity is violated in 4.0.16 under the following conditions:

-------------------------------------------------------------------
mysql> create table Parent (ssn int(9) primary key) type=innodb;
Query OK, 0 rows affected (0.05 sec)

mysql> create table Child (ssn int(9), index(ssn), foreign key(ssn) references Parent(ssn)) type=innodb;
Query OK, 0 rows affected (0.05 sec)

mysql> insert into Parent values (1);
Query OK, 1 row affected (0.05 sec)

mysql> insert into Child values (1);
Query OK, 1 row affected (0.03 sec)

mysql> delete from Parent;
ERROR 1217: Cannot delete or update a parent row: a foreign key constraint fails

mysql> drop table Parent;
Query OK, 0 rows affected (0.03 sec)
-------------------------------------------------------------------

Why is that we cannot delete the Parent record, but able to drop the whole table itself ?

Is this some kind of an open bug in 4.0.16 ?

Prasanna

No, this is a known and documented operation. When a "parent" table is dropped, any foreign keys that reference the table are dropped as well.

Oracle does not allow the user to drop the Parent table before dropping the referencing Child table.

In fact in MySQL, when we drop the Parent table, the foreign key constraints still exist in the Child table. Please look at the below list of statements. (Note that I dont even have the on delete cascade in the child table).

-----------------------------------------------------------------------------
mysql> create table parent (ssn int primary key) type=innodb;
Query OK, 0 rows affected (0.03 sec)

mysql> create table child (ssn int, index(ssn), foreign key(ssn) references parent(ssn)) type=innodb;
Query OK, 0 rows affected (0.05 sec)

mysql> insert into parent values(1);
Query OK, 1 row affected (0.09 sec)

mysql> insert into child values (1);
Query OK, 1 row affected (0.06 sec)

mysql> drop table parent;
Query OK, 0 rows affected (0.03 sec)

mysql> insert into child values (2);
ERROR 1216: Cannot add or update a child row: a foreign key constraint fails
------------------------------------------------------------------------------

Thanks,
Prasanna

I am sorry, the documentation states that when a table is dropped, the foreign key definitions that were created in *its* create statement are also dropped. That would explain your example, since the reference was created in another create statement.
I don't see this as a bug. SQL Server behaves the same as Oracle. A DBA should be the only one with permissions to drop a table that is required to maintain DB integrity. If a DBA is dropping a table, then there must be a reason and they will also take care to maintain the integrity of the database. What cases are there where this will cause a problem?

>> when a table is dropped, the foreign key definitions that were created in *its* create statement are also dropped.

Thats pretty obvious.

>> If a DBA is dropping a table, then there must be a reason and they will also take care to maintain the integrity of the database.

I dont quite accept your arguments. The point here is not whether there is a reason for the DBA to drop the table or not. Its about the database not assisting the DBA to maintain referential integrity.

"Even if what you say is right, there is NO reason for still keeping the foreign key constraints in the Child table".

I personally feel that this is a "bug" instead of calling it something like a "feature".

Anyway, Im beginning to love using the command line tools of MySQL.

Prasanna

I wouldn't call this a feature, either. I would call it a valid mode of operation. Why would a DBA need to have the database tell them that by dropping a whole table their database integrity would not be maintained? It seems like you want the DBMS to hold the hand of the DBA through administration tasks. I think that this method makes administration easier. Making a DBA drop the referential constraints on all the child tables before dropping the parent table, just to recreate the table (with modifications or whatever) and all the referential constraints is unecessary work with InnoDB. Even if InnoDB did not have this "bug," then a DBA who needs the database to tell them that what they are doing will mess up the integrity of the database can still screw it up. Once the DBA drops the constraints and the parent table, the child tables still don't have a parent! It is up to the DBA to restore referential integrity. It doesn't matter what the DBMS can do to protect referential integrity, if the DBA is working haphazardly, then the DB has no chance of survival.