In both Oracle and SQL Server, these non-deferrable constraints are evaluated at the statement completion, and it does allow intermediate state which could break constraint rules as long as final state at the statement completion is compliant.
PostgreSQL implements this differently, and the output can depends on the physical data orders.
PostgreSQL Example
mytest=# select version();
version
------------------------------------------------------------------------------------------------------------------
PostgreSQL 16.1 on x86_64-apple-darwin20.6.0, compiled by Apple clang version 12.0.5 (clang-1205.0.22.9), 64-bit
(1 row)
mytest=# drop table p;
DROP TABLE
mytest=# create table p (id int primary key);
CREATE TABLE
mytest=# insert into p values(1),(2),(3);
INSERT 0 3
mytest=# select * from p;
id
----
1
2
3
(3 rows)
mytest=# update p set id=id+1;
ERROR: duplicate key value violates unique constraint "p_pkey"
DETAIL: Key (id)=(2) already exists.
If we update the table backwards, it would succeed. As showed in followed code:
mytest=# do
$$
declare
rec_p record;
cur_p cursor for
select id from p order by id desc;
begin
open cur_p;
loop
fetch cur_p into rec_p;
exit when not found;
update p set id=id+1 where id=rec_p.id;
end loop;
end;
$$ language plpgsql;
DO
mytest=# select * from p;
id
----
4
3
2
(3 rows)
If the data in different physical order, it would succeed too.
mytest=# truncate table p;
TRUNCATE TABLE
mytest=# insert into p values(3),(2),(1);
INSERT 0 3
mytest=# select * from p;
id
----
3
2
1
(3 rows)
mytest=# update p set id=id+1;
UPDATE 3
mytest=# select * from p;
id
----
4
3
2
(3 rows)
MS SQL Server Example
[oracle@ol ~]$ sqlcmd -S localhost -U sa -C -P p_ssw0rd -d TestDB
1> select @@version;
2> go
-------------------------------------------------------------------
Microsoft SQL Server 2022 (RTM-CU11) (KB5032679) - 16.0.4105.2 (X64)
Nov 14 2023 18:33:19
Copyright (C) 2022 Microsoft Corporation
Developer Edition (64-bit) on Linux (Oracle Linux Server 9.3) <X64>
(1 rows affected)
1> create table p (id int primary key);
2> go
1> select * from t;
2> go
1> select * from p;
2> go
id
-----------
(0 rows affected)
1> insert into p values(1),(2),(3);
2> go
(3 rows affected)
1> update p set id=id+1;
2> go
(3 rows affected)
1> select * from p;
2> go
id
-----------
2
3
4
(3 rows affected)
Oracle Example
Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release Version 23.2.0.0.0
SQL> create table p (id int primary key);
Table created.
SQL> insert into p values(1),(2),(3);
3 rows created.
SQL> commit;
Commit complete.
SQL> update p set id=id+1;
3 rows updated.
SQL> commit;
Commit complete.
SQL> select * from p;
ID
----------
2
3
4
No comments:
Post a Comment