I have a table that has these key columns. Other columns are present but these are the key columns for this question.
user_id: string, external_id: string disabled_by: string
- All columns have the not null constraint.
- unique constraint ('external_id_constraint_1') (external_id, disabled_by)
- unique constraint ('external_id_constraint_2') (user_id, external_id)
Add external id
Adding an new external id for user 1 it would look like this:
insert into external_ids (user_id, external_id, disabled_by) values ('user1', 'exid2', '');
Disabling an external id
If the external id is later revoked for user 1:
update external_ids set disabled_by='admin1' where user_id='user1' and external_id='exid2';
Reenabling the external_id
A user can't have more than 1 entry for a given external_id. If a row exists ('user1', 'exid2', 'admin1') then the row needs to be updated:
update external_ids set disabled_by='' where user_id='user1' and external_id='exid2';
Rather than a new row being created. However another user between the disable and the reenable attempt could have claimed the external_id. If this has happened the update should do nothing.
I am trying to get a single postgres sql statement to handle this case.
Things I tried:
- an insert into .... on conflict on constraint ... except that I need to handle 2 different constraint violations differently.
- update does not have the ability to do the insert if there is nothing to update
- update doesn't have the ability to handle a constraint violation.
Is it possible to do this in a single sql statement or am I doomed to be frustrated.