Updating referenced columns in Postgres

Question

When there are one of more columns that reference another, I'm struggling for the best way to update that column while maintaining referential integrity. For example, if I have a table of labels and descriptions and two entries:

Label | Description
------------------------------------
read  | This item has been read
READ  | You read this thing already

Now, I don't want these duplicates. I want to add a constraint to the column that doesn't allow values that are case-insensitively duplicates, as in the example. However, I have several rows of several other tables referencing 'READ', the one I want to drop.

I know Postgres knows which fields of other rows are referencing this, because I can't delete it as long as they are there. So, how could I get any field referencing this to update to 'read'? This is just an example, and I actually have a few places I want to do this. Another example is actually an int primary key for a few tables, where I want to add a new table as a sort of 'base table' that the existing ones extend and so they'll all need to have unique IDs now, which means updating the ones they have.

I am open to recipes for functions I can add to do this, tools I can utilize, or anything else.

Answer 1

If you have many rows referencing READ, you could alter the foreign key to be on cascade update, update that table set Label = 'read' where Label = 'READ' and everything will get automagically fixed. After that you can alter the constraint again to be as it was before.

To find all the tables referencing the column, you can use

select TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME,
REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME from 
INFORMATION_SCHEMA.KEY_COLUMN_USAGE where
REFERENCED_TABLE_NAME = '<table>' AND REFERENCED_COLUMN_NAME = '<column>'

Answer 2

For the future you could create an unique index on the column "label", for example:

CREATE UNIQUE INDEX index_name ON table ((lower(label)));

Or check the manual. That would help you to avoid this situation for the next time.