Question 114957 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Update elements because foreign key column has changed

I have the following (simplified) structure of a device and log messages which are attached to a device:

CREATE TABLE device (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    serial_number VARCHAR(20) UNIQUE
);

CREATE TABLE error_log (
    id SERIAL PRIMARY KEY,
    device_id INT NOT NULL REFERENCES device(id),
);

I know want to change the schema to use the serial_number column of the device as the primary key, and drop the automatic key id. As part of the process I need to add a new column device_serial_number to the error_log table. My question is how to assign the values to this column using only SQL.

If a had an ORM mapper in action my pseudo code would look like this:

for log in error_log.all_objects():
    log.device_serial_number = log.device.serial_number

Can I do this in pure SQL? (Postgresql if that matters)

Tags:

2

Answers


  1. 0

    You need to update the rows in the error_log table where the device.id = error.log match to device.serial_number.

    So, first add a column to error_log. And then execute an UPDATE statement as following:

    update error_log
  set serial_number = d.serial_number
from device d 
where error_log.id = d.id;

    Afterwards you should add a UNIQUE constraint to the error_log.serial_number and then you can remove the error_log.device_id column if you don’t need it anymore.

    Login or Signup to reply.
  2. 0

    This is doable, just not in a single step.

    add the new column to the error_log table. This must be nullable for now!

    alter table error_log 
  add device_serial_number varchar(20);

    Populate the new column with the correct values

    update error_log
  set device_serial_number = d.serial_number
from device d
where error_log.device_id = d.id;

    Now the new column can be set to NOT NULL and we can remove the existing foreign key.

    alter table error_log
  alter column device_serial_number set not null,
  drop constraint error_log_device_id_fkey;

    Now drop the ID column, drop the old unique constraint and define a new primary key. Dropping the old unique constraint will remove the underlying unique index which is no longer needed as the primary key will create a new one.

    alter table device
   drop column id,
   drop constraint device_serial_number_key,
   add primary key (serial_number);

-- re-create the foreign key to the new primary key    
alter table error_log
   drop column device_id,
   add foreign key (device_serial_number) 
     references device(serial_number);

    Note that the drop constraint error_log_device_id_fkey and drop constraint device_serial_number_key assume the default naming convention that Postgres applies for "unnamed" constraints. You need to check if that really is the name of your foreign key.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search