Changing MySQL primary key when foreign key contraints exist

Question

I have two already-existing tables which look (in part) roughly like this:

CREATE TABLE parent (
    old_pk CHAR(8) NOT NULL PRIMARY KEY
) ENGINE=InnoDB;

CREATE TABLE child (
    parent_key CHAR(8),
    FOREIGN KEY (parent_key) REFERENCES parent(old_pk)
        ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB;

I want to add a new auto-incrementing integer id column to parent and use it as the primary key instead, while still keeping old_pk as a unique key and allowing other tables like child to reference it in foreign key contraints. Unfortunately, simply saying ALTER TABLE parent DROP PRIMARY KEY doesn't work:

Error Code : 1025

Error on rename of './data/#sql-4013_70f5e' to './data/parent' (errno: 150)

Some googling suggests that this is due to the existing foreign key reference from child. In essence, I need a way to tell MySQL "use this other column as the primary key, but don't forget the unique-key-ness of the original one". Is there any way to accomplish this, other than just dropping the key constraints from child and reinstating them afterwards?

Assume that I must alter the tables in place, rather than creating copies with the same data and swapping them in later. I've tried using SET FOREIGN_KEY_CHECKS = 0 before altering the table, but it does not seem to help.

Answer 1

I'll weigh in on this with what may be an unpopular suggestion. Don't use foreign key constraints in your database - enforce unique key and other constraints via TSQL in stored procedures (or... sigh... inline queries if you must, lol) as needed. It has been my experience that in scaled production environments check constraints are rarely used.

I say this with an open mind to opposing comments/discussion that may ensue. I'm not saying this suggestion is correct, just that it has been the prevailing opinion in the shops I've worked in.

Answer 2

Add an index (it could even be UNIQUE) to old_pk before dropping the primary key:

mysql> CREATE TABLE parent (
    ->     old_pk CHAR(8) NOT NULL PRIMARY KEY
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE child (
    ->     parent_key CHAR(8),
    ->     FOREIGN KEY (parent_key) REFERENCES parent(old_pk)
    ->         ON UPDATE CASCADE ON DELETE CASCADE
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO parent VALUES ('a');
Query OK, 1 row affected (0.01 sec)

mysql> CREATE INDEX old_pk_unique ON parent (old_pk);
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE parent DROP PRIMARY KEY;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> INSERT INTO child VALUES ('a');
Query OK, 1 row affected (0.00 sec)

mysql> SHOW CREATE TABLE parent;
+--------+------------------------------------------------------------------------------------------------------------------------------+
| Table  | Create Table                                                                                                                 |
+--------+------------------------------------------------------------------------------------------------------------------------------+
| parent | CREATE TABLE `parent` (
  `old_pk` char(8) NOT NULL,
  KEY `old_pk_unique` (`old_pk`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+--------+------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> INSERT INTO child VALUES ('b');
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test/child`, CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_key`) REFERENCES `parent` (`old_pk`) ON DELETE CASCADE ON UPDATE CASCADE)

mysql> INSERT INTO parent VALUES ('b');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO child VALUES ('b');
Query OK, 1 row affected (0.01 sec)

mysql> ALTER TABLE parent ADD id INT;
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> UPDATE parent SET id = 1 WHERE old_pk = 'a';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> UPDATE parent SET id = 2 WHERE old_pk = 'b';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> ALTER TABLE parent ADD PRIMARY KEY (id);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE parent;
+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table  | Create Table                                                                                                                                                                             |
+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| parent | CREATE TABLE `parent` (
  `old_pk` char(8) NOT NULL,
  `id` int(11) NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `old_pk_unique` (`old_pk`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)