Changement de MySQL clé primaire lors de la clé étrangère contraintes existent

J'ai deux déjà existants, les tableaux qui à l'air (en partie) à peu près comme ceci:

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;

Je veux ajouter une nouvelle auto-incrémentation entier id colonne de parent et de l'utiliser en tant que clé primaire au lieu de cela, tout en gardant old_pk comme une clé unique et permettant à d'autres tables comme child de référence dans les contraintes de clé étrangère. Malheureusement, il suffit de dire ALTER TABLE parent DROP PRIMARY KEY ne fonctionne pas:

Code D'Erreur : 1025

Erreur sur renommer". /données/#sql-4013_70f5e' à './données/parent' (errno: 150)

Quelques recherches sur google suggère que c'est en raison de la référence de clé étrangère de child. En fait, j'ai besoin d'un moyen de dire à MySQL "utiliser cette autre colonne comme clé primaire, mais n'oubliez pas l'unique clé "de l'original". Est-il un moyen pour accomplir cette, autre que juste de laisser tomber les contraintes de clé de child et de rétablir par la suite?

Suppose que je doit modifier les tables en place, plutôt que de créer des copies avec les mêmes données et l'échange d'eux plus tard. J'ai essayé d'utiliser SET FOREIGN_KEY_CHECKS = 0 avant de modifier le tableau, mais il ne semble pas aider.

c'est pourquoi je ne fais pas visible par l'utilisateur de la clé primaire (je suis pour la substitution de la clé primaire de l'obtenir-aller), l'utilisateur de l'évolution des exigences sont un mal de tête, mais il est bon de savoir que vous faites de votre utilisation de base de données de substitution clé primaire en.wikipedia.org/wiki/Surrogate_key

OriginalL'auteur wisnij | 2009-07-27

  1. 7

    Ajouter un index (il pourrait même être UNIQUE) à old_pk avant la suppression de la clé primaire:

    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)
    Ne fonctionne pas du tout pour moi.

    OriginalL'auteur inerte

  2. -7

    Je vais peser sur ce que peut être impopulaire suggestion. N'utilisez pas de contraintes de clés étrangères dans votre base de données - appliquer la clé unique et d'autres contraintes via TSQL dans des procédures stockées en tant que de besoin. C'est mon expérience que, dans l'échelle des environnements de vérifier les contraintes sont rarement utilisés.

    Je dis cela avec un esprit ouvert à la partie adverse commentaires/discussions qui pourraient s'ensuivre. Je ne dis pas que cette suggestion est correct, juste qu'il a été l'opinion dominante dans les magasins, j'ai travaillé dans l'.

    Une Demande: Si vous downvote moi, merci de laisser un petit commentaire. Dans les 10 dernières années j'ai travaillé avec des bases de données relationnelles, les seules personnes que je connais qui utilisent les contraintes de vérification sont à travailler sur des systèmes qui ne sont pas à l'échelle. Si ce sont les gens downvoting moi, alors je peux vivre avec ça. Mais si vous travaillez sur une échelle de système et de vérifier les contraintes de la norme pour vous, je voudrais savoir qui vous êtes afin que je puisse faire un peu de lecture pour voir ce que j'ai manqué.

    "à [max] échelle" pratiques != les meilleures pratiques.

    OriginalL'auteur codemonkey