CODEWITHSUNDEEP
sqlpostgresqlrenamepostgresql-8.3
Chickenmarkus
Chickenmarkus

Reputation: 1161

Rename VALUES of primary and foreign keys

There are two tables like this:

CREATE TABLE rooms (rid CHAR PRIMARY KEY);
CREATE TABLE users (uid INT PRIMARY KEY, rid CHAR FOREIGN KEY REFERENCES rooms(rid))

The target is to rename the keys (values, not table or column) of rid like this:

BEGIN TRANSACTION;
UPDATE rooms
SET rid = "9"||SUBSTRING(rid, 2)
WHERE TEXT(rid) LIKE "5%";
UPDATE users
SET rid = "9"||SUBSTRING(rid, 2)
WHERE TEXT(rid) LIKE "5%";
END TRANSACTION;

Of course this ends in an error of foreign key constraint.

In context of renaming oftn "sp_rename" is called. But I understood it in that way that it's only working with tables and columns, not on values.

How to rename the values of a foreign key constraint?

.

SOLVED with:

BEGIN TRANSACTION;

ALTER TABLE users
DROP CONSTRAINT users_rid_fkey,
ADD FOREIGN KEY (rid) REFERENCES rooms(rid) ON UPDATE CASCADE ON DELETE RESTRICT;

UPDATE rooms
SET rid = '9'||SUBSTRING(rid, 2)
WHERE rid LIKE '5%';

END TRANSACTION;

Upvotes: 2

Views: 893

Answers (1)

Denis de Bernardy
Denis de Bernardy

Reputation: 78433

You've several approaches… Namely:

  1. Drop the constraint, update your data, and re-add the constraint.

  2. Change the constraint so it is on update cascade.

  3. Change the constraint so as to make it deferrable initially deferred.

  4. Add a proper id column to rooms and reference that instead, to avoid the problem altogether.

Upvotes: 3

Related Questions