1) constraints: constraints Imposed on tables to ensure data integrity and prevent data loss in associated tables;
Common constraints:
-NOT NULL
-UNIQUE
-PRIMARY KEY
-FOREIGE KEY
-CHECK
When creating a table, specify the constraints. You can also add or modify the table after creating the table. The constraints can be column-level or table-level;
Reate table [schema.] table
(Column datatype [DEFAULT expr]
[Column_constraint],
...
[Table_constraint] [,...]);
2) Add Constraints
Alter table table
ADD [CONSTRAINT constraint] type (column );
3) Delete Constraints
Alter table table_name drop constraint constraint_name
4) Duplicate name Constraints
Alter table table_name rename constraint old_name to new_name
5) The constraint is valid/invalid.
Alter table table_name enable/disable constraint constraint_name;
Constraint extension is a function that checks the latency of primary key, unique key, and other constraints. You can use this function to delay the constraints check to the transaction commit, instead of checking immediately after the DDL operation. Therefore, the constraints check also has the following options: latency and instant;
SQL> create table aaa
2 (
3 c1 number constraint aaa_pk primary key deferrable initially immediate,
4 c2 number constraint c2_ck check (c2> 10) deferrable initially immediate
5 );
Table created.
SQL> set constraints c2_ck deferred;
Constraint set.
SQL> insert into aaa value (1, 1 );
Insert into aaa value (1, 1)
*
ERROR at line 1:
ORA-00928: missing SELECT keyword
SQL> insert into aaa values (1, 1 );
1 row created.
SQL> commit;
Commit
*
ERROR at line 1:
ORA-02091: transaction rolled back.
ORA-02290: check constraint (SCOTT. C2_CK) violated
When the request is submitted, the check will be constrained, that is, the delay check;
6) view Constraints
User_constraints
User_cons_columns