Oracle Constraint Status

First:

4 states of Oracle constraints:

Disable Novalidate Neither constrain new data nor validate existing data, equivalent to disable

Enable novalidate constrains new data but does not validate existing data

Disable validate constrain new data but do not validate existing data, disable DML when enabled

Enable validate constrains new data and verifies existing data, equivalent to enable

Test:

--1. Creating an Experiment Table
CREATE TABLE check_test as SELECT * from Scott.emp
--2. Query
SELECT * from Check_test;
--3. Adding constraints
ALTER TABLE check_test add constraint id_unique unique (empno);
--4. Viewing the loaded state of the current constraint
Select t.owner,t.constraint_name,t.status,t.deferrable,t.deferred,t.validated from User_constraints t where T.constraint_name= ' Id_unique '
T.owner,t.constraint_name,t.status, T.deferrable, t.deferred, t.validated
1testid_unique ENABLED not deferrable IMMEDIATE VALIDATED
See Status and VALIDATED are ENABLE VALIDATED respectively
--5. Insert operation after observing state (this data bit empno already exists.) ）
INSERT into check_test values (7934, ' EVAN ', ' Clerk ', 7782,to_date (' 1989/8/22 ', ' yyyy-mm-dd '), 6000,null,10);
Result: ORA-00001: Unique constraint violation (test.id_unique)
--6. Modify the status to:
ALTER TABLE check_test Modify constraint id_unique disable novalidate;
--7. Perform the insert operation again.
INSERT into check_test values (7934, ' EVAN ', ' Clerk ', 7782,to_date (' 1989/8/22 ', ' yyyy-mm-dd '), 6000,null,10);
Results: 1 Row inserted
The observation index will discover that the previous unique index was automatically deleted:
SELECT * from user_indexes where index_name= ' Id_unique '
--8. Modifying the state again
ALTER TABLE check_test Modify constraint id_unique enable novalidate;
Result: ORA-02299: Failed to validate (test.id_unique)-Found duplicate keyword.
Cause: Because enable will create a unique index, there is data deptno there is duplicate data 10, so there is no way to enable
--9. Delete duplicates.
Delete from check_test where empno=7934 and ename= ' EVAN ';
Execute again: ALTER TABLE check_test modify constraint id_unique enable novalidate;
Results: Table altered;
--10.
Select Index_name,table_name,uniqueness from dba_indexes where index_name = ' id_unique ';
Insert again:

INSERT into check_test values (7934, ' EVAN ', ' Clerk ', 7782,to_date (' 1989/8/22 ', ' yyyy-mm-dd '), 6000,null,10);
Result: ORA-00001: Unique constraint violation (test.id_unique)
--11. Changing the state again
ALTER TABLE check_test Modify constraint id_unique disable validate;
Insert data again: Result: ORA-25128: Cannot insert/update/delete table with disabled and validation constraints (Test.id_unique)

--12. Add a Constraint
ALTER TABLE check_test add constraint check_sal check (sal between 0 and 7000);
--12 Modifying a new constraint check_sal state
ALTER TABLE check_test Modify constraint check_sal disable novalidate;
Inserting data
INSERT into check_test values (7776, ' EVAN ', ' Clerk ', 7782,to_date (' 1989/8/22 ', ' yyyy-mm-dd '), 8000,null,10);
Commit
ALTER TABLE check_test Modify constraint check_sal enable novalidate;
INSERT into check_test values (7777, ' EVAN ', ' Clerk ', 7782,to_date (' 1989/8/22 ', ' yyyy-mm-dd '), 8000,null,10);
Result: ORA-02290: violation of CHECK constraint (test. Check_sal)
ALTER TABLE check_test Modify constraint check_sal enable validate;
Result: ORA-02293: Failed to validate (TEST. Check_sal)-Violation of CHECK constraint conditions

Second:

Constraint delay validation related content:

Deferrable Constraints

Every constraint is either in a not deferrable (default) or deferrable state. This state determines when Oracle Database checks the constraint for validity. The following graphic depicts the options for deferrable constraints

If a constraint is isn't deferrable, then Oracle Database never defers the validity check of the constraint to the end of th E transaction. Instead, the database checks the constraint at the end of each statement. If the constraint is violated and then the statement rolls back

You can set the default behavior for when the database checks the deferrable constraint. You can specify either of the following attributes:

• INITIALLY IMMEDIATE

  The database checks the constraint immediately after each statement executes. If the constraint is violated and then the database rolls back the statement.

• INITIALLY DEFERRED

  The database checks the constraint when a is COMMIT issued. If the constraint is violated and then the database rolls back the transaction

Test:

--1.
Select t.owner,t.constraint_name,t.deferrable,t.deferred from user_constraints t where t.constraint_name= ' CHECK_SAL '
Results:

OWNER constraint_name deferrable DEFERRED
------------------------------------------------------------ ------------------------------ -------------- ------- --
TEST Check_sal not deferrable IMMEDIATE

This constraint is not deferred

--2. Rebuilding Constraints Check_sal
ALTER TABLE check_test drop constraint check_sal;
Table Altered
ALTER TABLE check_test add constraint check_sal check (SAL between 1 and 7000) deferrable;
Table Altered
--3. Query again:
Select t.owner,t.constraint_name,t.deferrable,t.deferred from user_constraints t where t.constraint_name= ' CHECK_SAL '
Results:
OWNER constraint_name deferrable DEFERRED
------------------------------------------------------------ ------------------------------ -------------- ------- --
TEST check_sal deferrable IMMEDIATE
--4. Performing an Insert
INSERT into check_test values (7776, ' EVAN ', ' Clerk ', 7782,to_date (' 1989/8/22 ', ' yyyy-mm-dd '), 8000,null,10);
Result: ORA-02290: violation of CHECK constraint (test. Check_sal)
--5. Modifying constraint states:
Set constraint Check_sal DEFERRED;
Constraints Set
Insert data again: INSERT into check_test values (7776, ' EVAN ', ' Clerk ', 7782,to_date (' 1989/8/22 ', ' yyyy-mm-dd '), 8000,null,10);
Results: 1 Row inserted

Commit: Commit;
Results:
ORA-02091: Transaction fallback
ORA-02290: Violation of CHECK constraint (test. Check_sal)

Oracle Constraint Status