In a MySQL 5.0.45 database, I want to remove a 2-column UNIQUE constraint because it includes a column I am going to drop. One of the columns included in the two-column UNIQUE constraint is also FK-constrained in the same table - however I think that that is irrelevant to what follows.
The following example tables and data are fictional, but they mimic the form (contraints, relationships) of my real tables and result in the same problems when I try to modify them.
In the example, I want to drop the UNIQUE(library_id, external_id) constraint from book. (Because I want to drop the external_id column.)
-- Libraries
CREATE TABLE library (
id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50) NOT NULL UNIQUE
-- other columns
) ENGINE=INNODB;
-- Each book belongs to a library.
-- Each library has its own ID for the book (which we call
-- external_id). Each library should have allocated a
-- given external ID only once, but across libraries the
-- same external ID may have been allocated more than once.
CREATE TABLE book (
id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
library_id INTEGER NOT NULL,
CONSTRAINT book_library_id_fkey FOREIGN KEY(library_id) REFERENCES library(id),
external_id VARCHAR(20) NOT NULL,
UNIQUE(library_id, external_id)
-- other columns
) ENGINE=INNODB;
-- Made up example data
INSERT INTO library(id, name) VALUES
(1, "London"),
(2, "Cardiff"),
(3, "Dublin");
INSERT INTO book(id, name, library_id, external_id) VALUES
(1, "Programming Lisp", 1, "PL-42"),
(2, "Portable Lasers", 2, "PL-42"),
(3, "Programming Lisp", 2, "PL-43");
ATTEMPT 1: Directly dropping the unwanted column doesn't work:
> ALTER TABLE book DROP COLUMN external_id;
Error: Duplicate entry '2' for key 2
SQLState: 23000
ErrorCode: 1062
I believe this fails because dropping the column also drops the column from the UNIQUE constraint (i.e. index) which leaves that index with only one column indexed - and this now includes repeated values (2 x "PL-42").
ATTEMPT 2: Dropping the index only.
The following SO post suggests how to drop the index directly: Dropping Unique constraint from MySQL table. However, using SHOW INDEX FROM book lists two 'Key_name' entries with the same value, 'library_id':
> SHOW INDEX FROM book;
+-------+------------+--------------+-------------+
| Table | Key_name | Seq_in_index | Column_name |
+-------+------------+--------------+-------------+
| book | PRIMARY | 1 | id |
| book | library_id | 1 | library_id |
| book | library_id | 2 | external_id |
+-------+------------+--------------+-------------+
[Some columns ellided, for clarity.]
Doing as the post suggests then fails with a somewhat cryptic error, but presumably due to the ambiguity (it works in non-ambiguous cases):
> DROP INDEX library_id ON book;
Error: Error on rename of '.\a6_head_dev$nemo\#sql-bd8_1' to '.\a6_head_dev$nemo\book' (errno: 150)
SQLState: HY000
ErrorCode: 1025
(How do you drop an INDEX when SHOW INDEX reports duplicate values under Key_name?)
I have a fall-back option, which is to recreate the entire table without the external_id column and UNIQUE constraint, INSERT INTO everything from the original table, drop the old table and rename back.
But I'd like to know if there is a cleaner solution. I'd like to know if there is a better way to do this at all in MySQL (any version). But unfortunately I am stuck with 5.0 for the moment, so I really need a solution that works for that.
