Изменить первичный ключ в таблице PostgreSQL

Вопрос:У меня есть таблица users в моей базе данных PostgreSQL 9.3.6 с двумя столбцами: id и another_id. id является первичным ключом, another_id представляет собой просто еще один целочисленный столбец с уникальным ограничением. Существуют другие таблицы, которые ссылаются на пользователей по первичному ключу. Здесь описание таблицы users: Table "public.users" Column | Type | Modifiers | Storage

Вопрос:

У меня есть таблица users в моей базе данных PostgreSQL 9.3.6 с двумя столбцами: id и another_id. id является первичным ключом, another_id представляет собой просто еще один целочисленный столбец с уникальным ограничением.

Существуют другие таблицы, которые ссылаются на пользователей по первичному ключу.

Здесь описание таблицы users:

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

Здесь foo_table описание:

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

Как заменить первичный ключ в таблице PostgreSQL столбца id на столбец another_id и сохранить целостность данных?

Лучший ответ:

Я потратил некоторое время и, наконец, придумал рабочее решение.

Я опубликую его здесь для справок в будущем.

Решение

Прежде всего, у вас есть три таблицы (foo_table, bar_table, baz_table), которые указывают на вашу таблицу users с помощью внешних ключей (называемых user_id во всех случаях). Вам нужно будет заменить идентификаторы, хранящиеся в этих столбцах, от id до another_id. Вот как вы можете это сделать:

— 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;

Вам нужно будет повторить вышеуказанные запросы для каждой зависимой таблицы.

После этого все зависимые таблицы указывают на ваш новый столбец another_id.

В итоге нам просто нужно заменить первичный ключ:

— 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

Вы даже можете удалить исходный столбец id, если хотите.

Я надеюсь, что это поможет кому-то.

Оцените статью
Добавить комментарий