Oracle constraint Learning (1) unique and check

Some people say that there is no index. What can be used to guarantee the constraint? Regardless of whether this is true or false, many restrictions (except not null) are implemented by using indexes to quickly locate constraints. The unique constraint will automatically create an index, and the primary key is also. As a result, many constraints are always lingering with indexes.

 

Related views:

Dba_constraints
Dba_cons_columns

 

Not null constraints are special. The index type of not null is the same as the check constraint. Only the constraint not null is inherited. Remember to add constraints and indexes when copying a table.

 

Unique constraints
Latency verification attributes: deferrable/not deferrable, deferred/immediate

Latency constraints are verified only when submitted, most of which are the same.

Not deferrable: Check if not submitted. This is the default value.

SQL> select * from HR. T1; ID name ---------- ---------------------------------------- 1 D 2 bsql> conn HR/HR is connected. SQL> ALTER TABLE T1 add constraint t1_u_1 unique (ID); -- by default, the not deferrable & immediate table has been changed. SQL> insert into T1 values (1, 'C'); insert into T1 values (1, 'C') * row 1st error: ORA-00001: violation of unique constraints (HR. t1_u_1) SQL> ALTER TABLE T1 drop constraint t1_u_1 cascade; the table has been changed. SQL> ALTER TABLE T1 add constraint t1_u_2 unique (ID) deferrable; -- The deferrable & immediate table has been changed. SQL> select * from T1; ID name ---------- -------------------------------------- 1 D 2 bsql> insert into T1 values (1, 'C'); insert into T1 values (1, 'C ') * row 1st error: ORA-00001: violation of unique constraints (HR. t1_u_2)

Why is an error still reported when the delay occurs? Latency constraint. The index must be non-unique. Enable novalidate must also be a non-unique index. However, when creating the unique constraint, Oracle will create a unique index first, and the index name is the same as the constraint name. Create a constraint, unless this column already has a constraint.

SQL> ALTER TABLE T1 add constraint t1_u_6 unique (ID); the table has been changed. SQL> select index_name, Uniqueness from user_indexes; index_name uniqueness -------------------------------------------------------------------------- t1_u_6 unique

SQL> ALTER TABLE T1 add constraint t1_u_3 unique (ID) deferrable; -- The deferrable & immediate table has been changed. SQL> select * from T1; ID name ---------- -------------------------------------- 1 D 2 bsql> insert into T1 values (1, 'C'); insert into T1 values (1, 'C ') * row 1st error: ORA-00001: violation of unique constraints (HR. t1_u_3) SQL> ALTER TABLE T1 drop constraint t1_u_3 cascade; the table has been changed. SQL> drop index t1_index; the index has been deleted. SQL> create unique index t1_index on T1 (ID); the index has been created. SQL> select index_name, Uniqueness from user_indexes; index_name uniqueness limit t1_index uniquesql> ALTER TABLE T1 add constraint t1_u_4 unique (ID) deferrable; ALTER TABLE T1 add constraint t1_u_4 unique (ID) row deferrable * 1st error: ORA-01408: This column list indexed SQL> drop index t1_index; index deleted. SQL> ALTER TABLE T1 drop constraint t1_u_4 cascade; ALTER TABLE T1 drop constraint t1_u_4 cascade * row 1st error: ORA-02443: unable to delete constraints-nonexistent constraints SQL> ALTER TABLE T1 add constraint t1_u_5 unique (ID) initially deferred deferrable; -- if not deferrable, it can only be changed with the immediate table. SQL> select * from T1; ID name ---------- ---------------------------------------- 1 D 2 bsql> insert into T1 values (1, 'C'); 1 line has been created. SQL> insert into T1 values (1, 'D'); 1 row has been created. SQL> commit; Commit * 1st row error: ORA-02091: Transaction Processing rolled back ORA-00001: violation of unique constraints (HR. t1_u_5)

Immediate deferrable is an instant delay, while deferred deferrable is rolled back at commit.

Check

Validate checks the old value, but novalidate does not. This application can be used to avoid misoperations by the front-end lady.

 

SQL> select * from T1; ID name ---------- ---------------------------------------- 1 D 2 B 3 C 4 csql> ALTER TABLE T1 add constraint t1_u_6 check (ID> 10) Validate; alter table T1 add constraint t1_u_6 check (ID> 10) Validate * row 1st error: ORA-02293: unable to verify (HR. t1_u_6)-SQL statement that violates the check constraints> ALTER TABLE T1 add constraint t1_u_6 check (ID> 10) novalidate; the table has been changed. SQL> select * from T1; ID name ---------- -------------------------------------- 1 D 2 B 3 C 4 csql> insert into T1 values (5, 'D'); insert into T1 values (5, 'D') * row 1st error: ORA-02290: violation of check constraints (HR. t1_u_6) SQL> ALTER TABLE T1 add constraint t1_u_6 check (ID> 10) Enable novalidate; the table has been changed. SQL> insert into T1 values (6, 'D'); insert into T1 values (6, 'D') * row 1st error: ORA-02290: violation of check constraints (HR. t1_u_6) SQL> select * from T1; ID name ---------- -------------------------------------- 1 D 2 B 3 C 4 csql> ALTER TABLE T1 add constraint t1_u_6 check (ID> 10) Disable novalidate; the table has been changed. SQL> select * from T1; ID name ---------- ---------------------------------------- 1 D 2 B 3 C 4 csql> insert into T1 values (5, 's'); 1 line has been created. SQL> commit; submitted completely.

The priority of the constraint is higher than that of the attribute.

SQL> Create Table T1 (ID number, constraint t1_u_6 check (ID> 3) Validate); the table has been created. SQL> insert into T1 values (1); insert into T1 values (1) * row 1st error: ORA-02290: violation of check constraints (HR. t1_u_6) SQL> insert into T1 values (5); 1 row has been created. SQL> drop table T1 purge; the table has been deleted. SQL> Create Table T1 (ID number, constraint t1_u_6 check (ID> 3) novalidate); the table has been created. SQL> insert into T1 values (1); insert into T1 values (1) * row 1st error: ORA-02290: violation of check constraints (HR. t1_u_6) SQL> insert into T1 values (5); 1 row has been created. SQL> select * from T1; Id ---------- 5sql> ALTER TABLE T1 add constraint t1_u_6 check (ID> 10) novalidate; the table has been changed. SQL> insert into T1 values (3); insert into T1 values (3) * row 1st error: ORA-02290: violation of check constraints (HR. t1_u_6) SQL> insert into T1 values (7); insert into T1 values (7) * row 1st error: ORA-02290: violation of check constraints (HR. t1_u_6) SQL> insert into T1 values (11); 1 row has been created. SQL> select * from T1; Id ---------- 5 11