Modifier la clé primaire dans la table PostgreSQL

J'ai users table dans mon PostgreSQL 9.3.6 base de données avec deux colonnes: id et another_id. Le id est une clé primaire, la another_id est juste une autre colonne de type entier avec contrainte unique.

Il y a d'autres tables de référence des utilisateurs par clé primaire.

Voici la users description de la table:

Table "public.users"
        Column        |              Type              |               Modifiers                | Storage | Stats target | Description 
----------------------+--------------------------------+----------------------------------------+---------+--------------+-------------
 id                   | integer                        | not null                               | plain   |              | 
 another_id           | integer                        | not null                               | plain   |              | 

Indexes:
    "users_pkey" PRIMARY KEY, btree (id)
    "uniq_1483a5e93414710b" UNIQUE, btree (another_id)

Referenced by:
    TABLE "foo_table" CONSTRAINT "fk_4affc6e5a76ed395" FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
    TABLE "bar_table" CONSTRAINT "fk_72936b1da76ed395" FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
    TABLE "baz_table" CONSTRAINT "fk_83adbaf0a76ed395" FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE

Voici foo_table description:

Table "public.foo_table"
    Column    |              Type              |                   Modifiers                   | Storage  | Stats target | Description 
--------------+--------------------------------+-----------------------------------------------+----------+--------------+-------------
 id           | integer                        | not null                                      | plain    |              | 
 user_id      | integer                        |                                               | plain    |              | 

Indexes:
    "foo_table_pkey" PRIMARY KEY, btree (id)
    "idx_e52ffdeea76ed395" btree (user_id)
Foreign-key constraints:
    "fk_e52ffdeea76ed395" FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE

Comment puis-je remplacer la clé primaire dans la table PostgreSQL à partir de id colonne de another_id colonne et de maintenir l'intégrité des données?

  • Vous pouvez créer la clé primaire en utilisant les index unique (uniq_1483a5e93414710b), cela va vous changer pour un nouveau PENALTY.
  • btw, il faudra reconstruire de toutes référence à FK. et ce concirns l'intégrité - PG vous permet d'exécuter DDL dans la transaction. afin de fonctionner whoel ensemble de DDL entre début; FIN;
  • Voulez-vous supprimer complètement id de la colonne et de la remplacer par another_id, ou vous souhaitez à la fois d'être là? Si les deux, le PKs doit être "redirigé" vers another_id ou pas?
InformationsquelleAutor Slava Fomin II | 2015-03-16
  1. 52

    J'ai passé un certain temps et enfin est venu avec une solution de travail.

    Je la publierai ici pour référence future.

    Solution

    Tout d'abord, vous avez trois tables (foo_table, bar_table, baz_table) qui pointent vers votre users table par le biais de clés étrangères (appelé user_id dans tous les cas). Vous aurez besoin de remplacer les Identifiants stockés dans les colonnes de id à another_id. Voici comment vous pouvez le faire:

    -- We are dropping the foreign key constraint on dependant table (in other case it will prevent us from updating the values)
ALTER TABLE foo_table DROP CONSTRAINT fk_e52ffdeea76ed395;

-- Then, we're swapping values in foreign key column from id to another_id
UPDATE foo_table T SET user_id = (SELECT another_id FROM users WHERE id = T.user_id);

-- And finally we're creating new foreign key constraint pointing to the another_id instead of id
ALTER TABLE foo_table ADD CONSTRAINT fk_e52ffdeea76ed395 FOREIGN KEY (user_id) REFERENCES users (another_id) ON DELETE CASCADE;

    Vous devrez répéter les requêtes pour chaque table dépendante.

    Après que, tout dépendant de tables pointent vers votre nouveau another_id colonne.

    À la fin, on aura juste besoin de remplacer la clé primaire:

    -- 1. Dropping the original primary key
ALTER TABLE users DROP CONSTRAINT users_pkey

-- 2. Renaming existing index for another_id (optional)
ALTER INDEX uniq_1483a5e93414710b RENAME TO users_pkey

-- 3. Creating new primary key using existing index for another_id
ALTER TABLE users ADD PRIMARY KEY USING INDEX users_pkey

-- 4. Creating index for old id column (optional)
CREATE UNIQUE INDEX users_id ON users (id)

-- 5. You can drop the original sequence generator if you won't need it
DROP SEQUENCE users_id_seq

    Vous pouvez même déposer l'original id colonne si vous le souhaitez.

    J'espère que ça aidera quelqu'un.

    InformationsquelleAutor Slava Fomin II