Small research on several parameters of Oracle Constraints

First, let's clarify the following concepts: in ORACLE, constraints are divided into deferred and immediate2: deferred: If Oracle performs a check on constraints only when a transaction is committed (commit)

First, let's clarify the following concepts: in ORACLE, the constraints are divided into two types: deferred and immediate: deferred: If Oracle performs a check on the constraints only when the transaction is committed (commit)

First, let's clarify several concepts:

In Oracle, there are two constraints: deferred and immediate:
Deferred: If Oracle checks the constraints only when committing a transaction (commit), this constraint is called deferred ). If the data violates the latency constraint, the commit operation will cause the transaction to be rolled back (undo ).
Immediate: If the constraint is immediate (non-delayed), the constraint will be checked after the statement is executed. If the data violates the latency constraint, the statement is rolled back immediately.

In general, the constraints we use are initially immediate (default) and cannot be converted to deferred. However, if the initial state is deferrable (which must be manually specified), the two States of deferred and immediate can be converted at will.
In addition, there are four constraints:
ENABLE ensures that all input data complies with the constraints (constraint)
DISABLE always allows input data, regardless of whether the data complies with the constraints.
VALIDATE (verification) ensures that existing data complies with the constraints
NOVALIDATE (no verification) allows existing data to not comply with the constraints

Enable validate is the same as ENABLE. Oracle checks constraints and ensures that all data complies with the constraints.
Enable novalidate indicates that all newly inserted or modified data must comply with the constraints, but the existing data can not comply with the constraints.
Disable novalidate is the same as DISABLE. Oracle does not check constraints.
Disable validate will DISABLE the constraint, remove the index used by the constraint, and prohibit the modification of the data of the constraint key.

Any type of constraints must be enabled to take effect.

-- Comparison between deferred and immediate -----------------------------
SQL> drop table aa purge;

Table dropped.

SQL> create table aa (id number, name varchar2 (20), constraint pk primary key (id ));

Table created.

SQL> col constraint_name for a11
SQL> select CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME, STATUS, DEFERRABLE, DEFERRED, validated from u
Ser_constraints where table_name = 'a ';

CONSTRAINT _ C TABLE STATUS DEFERRABLE DEFERRED VALIDATED
-------------------------------------------------------------
PK P AA ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED

-- We can see that the default value is not deferrable. Next, convert it to immediate.

SQL> set constraint pk immediate;
Set constraint pk immediate
*
ERROR at line 1:
ORA-02447: cannot defer a constraint that is not deferrable

-- Add a uk instance and specify deferrable initially immidiate | deferred.

SQL> alter table aa add constraint uk unique (name) deferrable initially immediate;

Table altered.

SQL> select CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME, STATUS, DEFERRABLE, DEFERRED, validated from u
Ser_constraints where table_name = 'a ';

CONSTRAINT _ C TABLE STATUS DEFERRABLE DEFERRED VALIDATED
-------------------------------------------------------------
PK P AA ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED
UK U AA ENABLED DEFERRABLE IMMEDIATE VALIDATED

SQL> select * from aa;

No rows selected

SQL> insert into aa values (1, 'sdf ');

1 row created.

SQL> insert into aa values (2, 'sdf ');
Insert into aa values (2, 'sdf ')
*
ERROR at line 1:
ORA-00001: unique constraint (LYN. UK) violated

SQL> set constraints uk deferred;

Constraint set.

SQL> select * from aa;

No rows selected

SQL> insert into aa values (1, 'sdf ');

1 row created.

SQL> insert into aa values (2, 'sdf ');

1 row created.

SQL> commit;
Commit
*
ERROR at line 1:
ORA-02091: transaction rolled back.
ORA-00001: unique constraint (LYN. UK) violated