[MDEV-6907] Can't drop column for which a foreign key exists Created: 2014-10-21  Updated: 2014-10-22  Resolved: 2014-10-22

Status: Closed
Project: MariaDB Server
Component/s: Data Definition - Alter Table
Affects Version/s: 5.5.40
Fix Version/s: N/A

Type: Bug Priority: Minor
Reporter: Vitaliy Filippov Assignee: Unassigned
Resolution: Won't Fix Votes: 0
Labels: upstream
Environment:

Debian Wheezy 32-bit


 Description   

Hi!

In MariaDB, it's impossible to drop a column which references another table with a foreign key - i.e. not the REFERENCED column, but the one that REFERENCES other table. I find it strange - why MariaDB doesn't just drop the FK along with the dropped column?

> create table test1 (id int not null auto_increment primary key);
 
Query OK, 0 rows affected (0.01 sec)
 
> create table test2 (id int not null auto_increment primary key, b int, foreign key (b) references test1 (id) on update cascade);
 
Query OK, 0 rows affected (0.01 sec)
 
> alter table test2 drop b;
 
ERROR 1025 (HY000): Error on rename of './bugs3/#sql-4517_280f4' to './bugs3/test2' (errno: 150)
 
> show engine innodb status\G
 

------------------------
 
LATEST FOREIGN KEY ERROR
 
------------------------
 
141021 18:25:23 Error in foreign key constraint of table bugs3/test2:
 
there is no index in the table which would contain
 
the columns as the first columns, or the data types in the
 
table do not match the ones in the referenced table
 
or one of the ON ... SET NULL columns is declared NOT NULL. Constraint:
 
,
 
  CONSTRAINT "test2_ibfk_1" FOREIGN KEY ("b") REFERENCES "test1" ("id") ON UPDATE CASCADE



 Comments   
Comment by Elena Stepanova [ 2014-10-21 ]

The manual doesn't specify one way or another, so it's not clear whether it's a bug. I have my doubts. This implicit action would be obscure and more confusing than the current behavior, and an attempt to do it this way would cause lots of questions, e.g.

  • what should happen if there are several columns in the FK, and only one of them is being dropped? Should the whole FK be dropped too? It's very intrusive. Should the FK be re-created without the column? It's not always possible.
  • what should happen if a column is not dropped, but modified?
    and so on.

And the only gain would be that a DBA does not have to perform an extra step dropping the FK when they encounter the error?

In any case, the current behavior comes from upstream. If you feel strong about this, please try to file a bug report at bugs.mysql.com – if they decide to implement it, eventually it will make it to MariaDB too.

Comment by Vitaliy Filippov [ 2014-10-21 ]

Hm... sorry. I had a strange feeling that upstream MySQL just drop FKs in this case... It seems I was incorrect. Feel free to close the issue. It's PostgreSQL that drops FKs in this case.

Generated at Thu Feb 08 07:15:29 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.