Oracle Management Constraints (i) Oracle constraints Basic concepts

The integrity of the data is used to ensure that database data follows specific business rules.

In an Oracle database, you can use constraints, triggers, and application code (procedures, functions) to achieve data integrity in 3 ways.

Today we mainly discuss how to use constraints to ensure data integrity and consistency.

1, the function of the constraint

Through a number of mandatory business rules to ensure data integrity, consistency

2, the type of constraint

1) NOT NULL

2) Check

3) Unique

4) primary KEY (NOT NULL + unique)

5) foreign key (reference)

3, the state of the constraint

1) ENABLE VALIDATE (default state)

When set to this state, the constraint takes effect immediately and requires that new and old data must satisfy the constraint rules at the same time

11:43:19 sql> ALTER TABLE EMP1

11:44:19 2 enable validate constraint pk_emp1;

ALTER TABLE EMP1

*

ERROR at line 1:

Ora-02437:cannot Validate, SCOTT. PK_EMP1)-primary key violated

-----both old and new records must conform to constraints

2) ENABLE Novalidate

When set to this state, the constraint takes effect immediately, and the existing data may not satisfy the constraint rules, but the new data must satisfy the constraint rules.

Can be used directly for not NULL, check, FOREIGN KEY constraint, and for primary key, unique constraints cannot be used directly because a unique index is generated for primary key and unique, and this state cannot be used; To use this state , you must use a non-unique index.

11:37:46 sql> CREATE TABLE EMP1 as SELECT * from EMP;

Table created.

11:37:51 sql> SELECT * from EMP1;

EMPNO ename JOB MGR hiredate SAL COMM DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

7369 SMITH Clerk 7902 17-dec-80 800 20

7499 ALLEN salesman 7698 20-feb-81 1600 300 30

7521 WARD salesman 7698 22-feb-81 1250 500 30

7566 JONES MANAGER 7839 02-apr-81 2975 20

7654 MARTIN salesman 7698 28-sep-81 1250 1400 30

7698 BLAKE MANAGER 7839 01-may-81 2850 30

7782 CLARK MANAGER 7839 09-jun-81 2450 10

7788 SCOTT ANALYST 7566 19-apr-87 3000 20

7839 KING PRESIDENT 17-nov-81 5000 10

7844 TURNER salesman 7698 08-sep-81 1500 0 30

7876 ADAMS Clerk 7788 23-may-87 1100 20

7900 JAMES Clerk 7698 03-dec-81 950 30

7902 FORD ANALYST 7566 03-dec-81 3000 20

7934 MILLER Clerk 7782 23-jan-82 1300 10

11:38:04 sql> Update emp1 set empno=7788 where ename= ' KING ';

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/

1 row updated.

11:39:00 sql> SELECT * from Emp1 WHERE empno=7788;

EMPNO ename JOB MGR hiredate SAL COMM DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

7788 SCOTT ANALYST 7566 19-apr-87 3000 20

7788 KING PRESIDENT 17-nov-81 5000 10

11:39:10 sql>

--Add primary key to enable Novalidate

11:39:10 sql> ALTER TABLE EMP1 add constraint

11:39:42 2 PK_EMP1 primary KEY (empno) enable novalidate;

PK_EMP1 primary KEY (empno) enable Novalidate

*

ERROR at line 2:

Ora-02437:cannot Validate, SCOTT. PK_EMP1)-primary key violated

--reason: in establishing primary key it needs to establish unique index

--Indexing on empno (non-unique index)

11:40:06 sql> CREATE index emp1_empno_ind on EMP1 (empno) tablespace indexes;

Index created.

11:42:17 sql> ALTER TABLE EMP1 add constraint

11:42:20 2 PK_EMP1 primary KEY (EMPNO) using index to enable novalidate;

Table altered.

11:42:29 sql>

--The new record must conform to the constraint, the old does not check

11:42:58 sql> Update emp1 set empno=7788 where ename= ' FORD ';

Update EMP1 set empno=7788 where ename= ' FORD '

*

ERROR at line 1:

Ora-00001:unique constraint, SCOTT. PK_EMP1) violated

3) DISABLE VALIDATE

Any DML operations on a table are not allowed when the constraint is set to this state. For primary keys and uniqueness constraints, the corresponding unique index is deleted, but the constraint rules are still valid.

4) DISABLE Novalidate

When you set a constraint to this state, the data can not satisfy the constraint rule. For primary keys and uniqueness constraints, the corresponding unique index is deleted.