Rename VALUES of primary and foreign keys

Question

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;

score 3 · Accepted Answer · answered Nov 09 '13 at 09:53

You've several approaches… Namely:

Drop the constraint, update your data, and re-add the constraint.
Change the constraint so it is on update cascade.
Change the constraint so as to make it deferrable initially deferred.
Add a proper id column to rooms and reference that instead, to avoid the problem altogether.

Rename VALUES of primary and foreign keys

1 Answers1