How to check column uniqueness on COMMIT

[patricebender]

Hello Colleagues,

I know that for referential constraints it is possible to defer the constraint check to the COMMIT of a transaction:

CREATE TABLE E_texts (
  ID_texts Integer,
  locale NVARCHAR(14),
  PRIMARY KEY(ID_texts),
  CONSTRAINT BLA UNIQUE (locale),
  CONSTRAINT BLUB FOREIGN KEY (ID_TEXTS)
  REFERENCES E_Texts (ID_TEXTS)
);

-- defer referential constraint check time to COMMIT ✅
ALTER TABLE E_texts ALTER CONSTRAINT BLUB INITIALLY DEFERRED
-- defer unique constraint check time to COMMIT ❌
ALTER TABLE E_texts ALTER CONSTRAINT BLA INITIALLY DEFERRED
> SQL Error [397] [HY000]: SAP DBTech JDBC: [397] (at 37): invalid object name: enabled only for foreign key constraints: line 1 col 38 (at pos 37)

The error is pretty clear, this feature seems only to be available for foreign key constraints. However, I do not fully understand this restriction. For other DBMS like PostgreSQL, this is possible.

Is there a workaround available? Is this feature maybe on some kind of roadmap?

Best Regards,

Patrice

[lbreddemann]

I'm not aware of any plans for adding deferred unique constraints.

What I am more interested in is: what is the use case for this?

Deferred referential constraints are obviously useful for data loading scenarios. In these cases, I don't have to sequence the loading of table data but can load in parallel.

But for unique constraints, why would I want to wait until the very end of the transaction to encounter a "duplicate value" error?

----

Ok, now that the OP shared that the actual problem to be solved is to swap values of records on a column with a unique constraint, a solution can be found.

create table item_prio 
   ( item_id bigint primary key
   , seq integer unique);

insert into item_prio values (1, NULL);
insert into item_prio values (2, NULL);
insert into item_prio values (3, NULL);
insert into item_prio values (4, NULL);

update item_prio set seq = 1 where item_id = 3;
update item_prio set seq = 1 where item_id = 2;

/*
Started: 2021-07-22 19:13:51
Could not execute 'update item_prio set seq = 1 where item_id = 2' in 28 ms 247 µs . 
[301]: unique constraint violated: Table(ITEM_PRIO), Index(_SYS_TREE_CS_#185746_#0_#1) with error: unique constraint violation for table DEVDUDE:ITEM_PRIO$delta_1$en, constraint='SEQ', key value='2', pos=0; indexname=_SYS_TREE_CS_#185746_#0_#1 
*/

-- OK, so this leads correctly to an error...

update item_prio set seq = 2 where item_id = 2;

select * from item_prio order by seq asc nulls last;
/*
ITEM_ID SEQ
3       1    <--- we want to swap these
2       2    <---  
1       ?  
4       ?  
*/

update item_prio 
    set seq = 1 
    where item_id = 1;
    
/*
Started: 2021-07-22 19:17:55
Could not execute 'update item_prio set seq = 1 where item_id = 1' in 1 ms 720 µs . 
[301]: unique constraint violated: Table(ITEM_PRIO), Index(_SYS_TREE_CS_#185746_#0_#1) with error: unique constraint violation for table DEVDUDE:ITEM_PRIO$delta_1$en, constraint='SEQ', key value='1', pos=0; indexname=_SYS_TREE_CS_#185746_#0_#1 
*/

-- DING, DING, thanks for playing.
-- standard SQL uniqueness is consistent even during transactions.

-- maybe a "set"-based UPDATE?

update item_prio n
    set seq = o.seq
    from item_prio n, item_prio o
    where n.item_id != o.item_id
    and o.seq in (1,2); 

/* [301]: unique constraint violated: Table(ITEM_PRIO), Index(_SYS_TREE_CS_#185746_#0_#1) with error: unique constraint violation in self check for table HXE::DEVDUDE:ITEM_PRIOen, constraint='SEQ', udiv='31', pos=3; indexname=_SYS_TREE_CS_#185746_#0_#1 
*/

-- DING, DING, please enter coin to continue
-- Alright, what else could we try? MERGE ??

merge into 
        item_prio n
 using item_prio o 
      on n.item_id != o.item_id
     and o.seq in (1, 2)
     and n.seq in (1, 2)
when matched then update set n.seq = o.seq;

-- Yup, that seems to work just alright.
-- Thanks for playing, enter HIGHSCORE ... L B ...
/*
ITEM_ID SEQ
2       1   <--- 3 and 2 did the swap-aroo!
3       2   <--- 
1       ?  
4       ?  
*/

[patricebender]

Given a unique constrained column, try to swap the values for two entries. Observe the error 🙂