Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Minor
-
Resolution: Won't Fix
-
Affects Version/s: 5.5.40
-
Fix Version/s: N/A
-
Component/s: Data Definition - Alter Table
-
Labels:
-
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
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
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.
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.
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.