Update multiple records, with temporary key overlap

Question

I've a table on DB with the following fields as PK: DOC_ID, SECTION_ID, SET_ID, CELL_ID. As you can deduce, this is referred to a set of Excel spreadsheets.

In addition to those fields, I have a CELL_ROW field and CELL_COL field. With the SET_ID (unique), they form an alternative key.

So, al least in theory, if I want to swap two cells' coordinates, I need either to release that constraint, or use a third temporary free position (say A100000 for example).

Suppose I have the following cells:

 DOC_ID | SECTION_ID | SET_ID | CELL_ID | CELL_ROW | CELL_COL
--------|------------|--------|---------|----------|----------
   5    |     456    |  8778  |  15045  |    5     |    4
   5    |     456    |  8778  |  15048  |    5     |    5

And suppose I have the following temporary table from which I perform an UPDATE to the main table:

 DOC_ID | SECTION_ID | SET_ID | CELL_ID | CELL_ROW | CELL_COL
--------|------------|--------|---------|----------|----------
   5    |     456    |  8778  |  15045  |    5     |    5
   5    |     456    |  8778  |  15048  |    5     |    4

In theory, that UPDATE should raise an exception...

But just tried, it works!

Can you explain me why? Does Oracle performs it as an atomic operation, so constraints are checked only after the whole operation (instead of record per record)?

How MS SQL Server 2008 r2 and Postgres behave in this kind of situations?

score 1 · Answer 1 · answered Feb 26 '13 at 12:53

I tried it in Postgresql and as expected a duplicate key error is raised:

create table t (
    doc_id integer,
    section_id integer,
    set_id integer,
    cell_id integer,
    cell_row integer,
    cell_col integer,
    primary key (doc_id, section_id, set_id, cell_id),
    unique (set_id, cell_row, cell_col)
);

insert into t (doc_id, section_id, set_id, cell_id, cell_row, cell_col)
values
(5, 456, 8778, 15045, 5, 4),
(5, 456, 8778, 15048, 5, 5);

create temporary table t_temp (
    doc_id integer,
    section_id integer,
    set_id integer,
    cell_id integer,
    cell_row integer,
    cell_col integer
);

insert into t_temp (doc_id, section_id, set_id, cell_id, cell_row, cell_col)
values
(5, 456, 8778, 15045, 5, 5),
(5, 456, 8778, 15048, 5, 4);

update t
set
    cell_col = t_temp.cell_col
from t_temp
where
    t.doc_id = t_temp.doc_id
    and t.section_id = t_temp.section_id
    and t.set_id = t_temp.set_id
    and t.cell_id = t_temp.cell_id
;
ERROR:  duplicate key value violates unique constraint "t_set_id_cell_row_cell_col_key"
DETAIL:  Key (set_id, cell_row, cell_col)=(8778, 5, 5) already exists.

I could do it if the constraint was set as deferrable. Check your create table statement for that keyword.

score 1 · Accepted Answer · answered Feb 26 '13 at 13:04

1

PostgreSQL will throw an error, unless you defer the unique constraint (and then you can't use it as a foreign key). I am not sure about SQL Server.

As a brief aside, you probably want to call it Postgres or PostgreSQL. Calling it Postgre is a sign that you have had too little contact with the community to be corrected.

More info on PostgreSQL

PostgreSQL checks tuple constraints at tuple update time. This means that unique constraints will be violated even in cases where a set is updated in an atomic way that does not violate a unique constraint. This leads to some interesting workarounds such as multiplying an integer key by -1 for the set and then in the next update multiply by -1 again and adding one.

answered Feb 26 '13 at 13:04

Chris Travers

25,424
6
65
182

Thanks for the answer! Actually, I had no contact with the Postgres community. In my company, we provide full support for the mentioned DBMSs, but I've never worked on Postgres since my hiring one year ago. – Teejay Feb 26 '13 at 13:16
I tried on SQL Server and it seems to work without issues. Now, for PG, is there a way to alter a constraint and set it `DEFERRABLE` on the fly? Or this attribute could be set only at table creation? Note that the constraint is not a FK. – Teejay Feb 26 '13 at 15:19
Decided to set it DEFERRABLE at DB creating to solve the problem. – Teejay Feb 26 '13 at 23:34

Update multiple records, with temporary key overlap

2 Answers2