Update elements because foreign key column has changed-CodePudding

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)

CodePudding user response：

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.

CodePudding user response：

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.