Oracle latency Constraints

Many times, oracle only knows the constraints to restrict some data to complete the business logic. However, it is found that there are also application latency constraints. the scenario is as follows: the inventory table uses the product ID as the unique identifier, but at a certain time point, that is, after the product of a specific ID is sold out, it then enters the product (the processing of business logic ), that is, two identical product IDs may exist temporarily in a transaction. in this case, we need to apply the unique latency constraint. the experiment is as follows: -- test table [SQL] 11:33:43 SCOTT @ orcl> DESC T02 name is blank? Type -------- ---------------------------------- id number sal number 11:35:23 SCOTT @ orcl> SELECT * FROM T02; id sal ---------- 1 1000 2 2000 3 6000 4 1000 5 2000 6 6000 already selected 6 rows. Used time: 00: 00: 00.01 -- Experiment (latency verification, verification when transaction is committed) [SQL] 11:37:18 SCOTT @ orcl> ALTER TABLE T02 ADD constraint con_uni_id UNIQUE (ID) INITIALLY DEFERRED; the table has been changed. Used time: 00: 00: 00.56 11:37:46 SCOTT @ orcl> insert into t02 select 5, 2152 from dual; 1 row has been created. Used time: 00: 00: 00.03 11:38:13 SCOTT @ orcl> select * from t02 where id = 5; id sal ---------- 5 2152 5 2000 two rows have been selected. Used time: 00: 00: 00.03 11:38:30 SCOTT @ orcl> commit; commit * 1st row error: ORA-02091: transaction processed rolled back ORA-00001: violation of unique constraints (SCOTT. CON_UNI_ID) used time: 00: 00: 00.04 -- verify immediately (default, that is, this verification method is also used without INITIALLY immediate) [SQL] 11:38:35 SCOTT @ orcl> ALTER TABLE T02 drop constraint con_uni_id; the TABLE has been changed. Used time: 00: 00: 00.06 11:40:56 SCOTT @ orcl> alter table T02 ADD constraint con_uni_id UNIQUE (ID) INITIALLY immediate; the TABLE has been changed. Used time: 00: 00: 00.07 11:41:36 SCOTT @ orcl> insert into t02 select 5, 2152 from dual; insert into t02 select 5, 2152 from dual * 1st Line Error: ORA-00001: violation of the unique constraint (SCOTT. CON_UNI_ID) used time: 00: 00: 00.01