Oracle's constraints are mainly to maintain data integrity at the business logic level. Mainly by programmers to specify constraints in the application or by defining triggers to maintain data integrity, and finally by using Oracle's own constraints to maintain data integrity. If you can use Oracle's own constraints to meet the requirements, try to use Oracle's own constraints, because the use of user-defined constraints such as triggers will affect the performance of the database. For example: when using triggers, the table will be locked and table scans or index scans will be performed, which will reduce database performance and concurrency.
Oracle constraints are mainly divided into the following types:
not null non-null constraint, unique unique constraint, primary key primary key constraint, foreign key foreign key constraint, check constraint.
not null non-null constraint:
Creation method: 1. Define at the column level when creating the table (that is, you can only write the definition of the constraint after the column when defining the table), 2 methods. One uses custom constraint names, and the other uses system default names.
view plaincopy to clipboardprint?
create table t
(
tid number(8) constraint NK_t1 not null,
tname varchar2(10) not null
)
create table t
(
tid number(8) constraint NK_t1 not null,
tname varchar2(10) not null
)
view plaincopy to clipboardprint?
SQL> select t.constraint_name, t.table_name, t.status, t.deferrable from user_constraints t;
CONSTRAINT_NAME TABLE_NAME STATUS DEFERRABLE
-------------------------------------------------------- -------------------
NK_T1 T ENABLED NOT DEFERRABLE
SYS_C003735 T ENABLED NOT DEFERRABLE
SQL>
SQL> select t.constraint_name, t.table_name, t.status, t.deferrable from user_constraints t;
CONSTRAINT_NAME TABLE_NAME STATUS DEFERRABLE
-------------------------------------------------------- ----------------
NK_T1 T ENABLED NOT DEFERRABLE
SYS_C003735 T ENABLED NOT DEFERRABLE
SQL>
2. Modify the table after it is created, but make sure that the data in the table does not violate constraints.
view plaincopy to clipboardprint?
SQL> alter table t modify tid not null;
Table altered
SQL> select t.constraint_name, t.table_name, t.status, t.deferrable from user_constraints t;
CONSTRAINT_NAME TABLE_NAME STATUS DEFERRABLE
-------------------------------------------------------- ----------------
SYS_C003736 T ENABLED NOT DEFERRABLE
SQL>
SQL> alter table t modify tid not null;
Table altered
SQL> select t.constraint_name, t.table_name, t.status, t.deferrable from user_constraints t;
CONSTRAINT_NAME TABLE_NAME STATUS DEFERRABLE
-------------------------------------------------------- -------------------
SYS_C003736 T ENABLED NOT DEFERRABLE
SQL>
check constraints
Creation method: It can be defined at the table level and column level (it can be defined after the column or after the column is defined). There are also 2 ways of definition.
view plaincopy to clipboardprint?
SQL> create table t
2 (
3 tid number(8) ,
4 tname varchar2(10),
5 constraint CK_T1 check ((tid is not null) and (tid > 0))
6)
7/
Table created
SQL> alter table t add constraint CK_T2 check(tname is not null);
Table altered
SQL> select t.constraint_name, t.table_name, t.status, t.deferrable from user_constraints t;
CONSTRAINT_NAME TABLE_NAME STATUS DEFERRABLE
-------------------------------------------------------- -------------------
CK_T1 T ENABLED NOT DEFERRABLE
CK_T2 T ENABLED NOT DEFERRABLE
SQL>
SQL> create table t
2 (
3 tid number(8) ,
4 tname varchar2(10),
5 constraint CK_T1 check ((tid is not null) and (tid > 0))
6)
7/
Table created
SQL> alter table t add constraint CK_T2 check(tname is not null);
Table altered
SQL> select t.constraint_name, t.table_name, t.status, t.deferrable from user_constraints t;
CONSTRAINT_NAME TABLE_NAME STATUS DEFERRABLE
-------------------------------------------------------- ----------------
CK_T1 T ENABLED NOT DEFERRABLE
CK_T2 T ENABLED NOT DEFERRABLE
SQL>
Constraint CK_T1 ensures that the tid column cannot be empty and is greater than 0, and CK_T2 ensures that iname cannot be empty. Check constraints can also be rules between different columns of the same row.
unique constraint
Creation method: If a unique column contains only one column, it can be defined at the column level. If a unique column contains multiple columns, it can only be defined at the table level.
view plaincopy to clipboardprint?
SQL> create table t2
2 (
3 vid number constraint VK_T1 unique,
4 vname varchar2(10),
5 vsex varchar2(10),
6 constraint VK_T2 unique(vname,vsex)
7)
8/
Table created
SQL> select t.constraint_name, t.table_name, t.status, t.validated, t.deferrable from user_constraints t;
CONSTRAINT_NAME TABLE_NAME STATUS VALIDATED DEFERRABLE
--------------- ---------- -------- ---------- ------- -------
VK_T1 T2 ENABLED VALIDATED NOT DEFERRABLE
VK_T2 T2 ENABLED VALIDATED NOT DEFERRABLE
SQL>
SQL> create table t2
2 (
3 vid number constraint VK_T1 unique,
4 vname varchar2(10),
5 vsex varchar2(10),
6 constraint VK_T2 unique(vname,vsex)
7)
8/
Table created
SQL> select t.constraint_name, t.table_name, t.status, t.validated, t.deferrable from user_constraints t;
CONSTRAINT_NAME TABLE_NAME STATUS VALIDATED DEFERRABLE
--------------- ---------- -------- ---------- ------- -------
VK_T1 T2 ENABLED VALIDATED NOT DEFERRABLE
VK_T2 T2 ENABLED VALIDATED NOT DEFERRABLE
SQL>
When the unique constraint is created, an index will be generated (it may be a unique index, so it may also be a non-unique index, depending on whether the table is specified when creating the table to check the data constraints immediately when the data is inserted):
view plaincopy to clipboardprint?
SQL> select t.index_name, t.table_name, t.uniqueness from user_indexes t;
INDEX_NAME TABLE_NAME UNIQUENESS
---------- ---------- ----------
VK_T1 T2 UNIQUE
VK_T2 T2 UNIQUE
SQL> select t.index_name, t.table_name, t.uniqueness from user_indexes t;
INDEX_NAME TABLE_NAME UNIQUENESS
---------- ---------- ----------
VK_T1 T2 UNIQUE
VK_T2 T2 UNIQUE
Since there is an index, you can specify the index storage location and some storage parameters when creating the table.
view plaincopy to clipboardprint?
SQL> create table t2
2 (
3 vid number constraint VK_T1 unique,
4 vname varchar2(10),
5 vsex varchar2(10),
6 constraint VK_T2 unique(vname,vsex) using index tablespace indx
7 storage(initial 100k next 100k pctincrease 0)
8 nologging
9)
10/
Table created
SQL> create table t2
2 (
3 vid number constraint VK_T1 unique,
4 vname varchar2(10),
5 vsex varchar2(10),
6 constraint VK_T2 unique(vname,vsex) using index tablespace indx
7 storage(initial 100k next 100k pctincrease 0)
8 nologging
9)
10/
Table created
The table space where the constrained index is stored is the indx table space, the initial block size is 100k, and no logs are generated for dml operations (but logs are also generated for other reasons, but there are fewer logs than the default logging)
primary key primary key constraint
Creation method: The primary key is mainly composed of non-null and uniqueness. A table can only contain one primary key, but a primary key can contain multiple columns.
view plaincopy to clipboardprint?
SQL> create table t2
2 (
3 vid number constraint VK_T1 unique,
4 vname varchar2(10),
5 vsex varchar2(10),
6 constraint VK_T2 primary key(vname,vsex) using index tablespace indx
7 storage(initial 100k next 100k pctincrease 0)
8 nologging
9)
10/
Table created
SQL> create table t2
2 (
3 vid number constraint VK_T1 unique,
4 vname varchar2(10),
5 vsex varchar2(10),
6 constraint VK_T2 primary key(vname,vsex) using index tablespace indx
7 storage(initial 100k next 100k pctincrease 0)
8 nologging
9)
10/
Table created
foreign key foreign key
Creation method: The tables involved in the foreign key can have two or one table. In the case of two tables, the key referenced by a field in a child table must be the primary key in the fatter table. The constraint is established in the child table, indicating that the value in the field in the table must exist in the parent table or be a NULL value.
view plaincopy to clipboardprint?
SQL> create table dept
2 (
3 did number(8),
4 dname varchar2(20),
5 constraint PK_DEPT primary key (did)
6)
7/
Table created
SQL>
SQL> create table emp
2 (
3 eid number(8) primary key,
4 ename varchar2(20),
5 did number(8) /*references dept(did)*/,
6 dname varchar2(20),
7 constraint FK_EMP2 foreign key(did) references dept(did)
8)
9/
Table created
SQL> create table dept
2 (
3 did number(8),
4 dname varchar2(20),
5 constraint PK_DEPT primary key (did)
6)
7/
Table created
SQL>
SQL> create table emp
2 (
3 eid number(8) primary key,
4 ename varchar2(20),
5 did number(8) /*references dept(did)*/,
6 dname varchar2(20),
7 constraint FK_EMP2 foreign key(did) references dept(did)
8)
9/
Table created
Since the foreign key (foreign key) is determined by the reference key (reference key), there will be restrictions when performing update, drop, delete and other operations on the fater table. Specifically, it is determined by the status of foreign key constraints.
delete on action, delete cascade, delete set null
Delete on action is the default setting. If you delete the data in the fater table, Oracle will lock the word table and then scan the table (if there is a foreign key column, scan the index) and then the data will not exist in the foreign key column in the child table. Delete allowed.
delete cascade also deletes the corresponding row in the word table.
delete set null, the data corresponding to the foreign key column in the bar table becomes NULL.
view plaincopy to clipboardprint?
create table emp
(
eid number(8) primary key,
ename varchar2(20),
did number(8) /*references dept(did)*/,
dname varchar2(20),
constraint FK_EMP2 foreign key(did) references dept(did) on delete set null
)
create table emp
(
eid number(8) primary key,
ename varchar2(20),
did number(8) /*references dept(did)*/,
dname varchar2(20),
constraint FK_EMP2 foreign key(did) references dept(did) on delete set null
)