oracle查询约束定义,Oracle约束

导读：什么是约束?约束是表级强制执行的规则，当表中数据有相互依赖性时，可保护数据不被删除。Oracle数据库是一种功能性很强大的数据库系统，Oracle数据库中的数据是绝对要保密的，下文中就为大家介绍Oracle数据库的约束问题。

Oracle 有如下类型的约束:

NOT NULL

UNIQUE Key

PRIMARY KEY

FOREIGN KEY

CHECK

Oracle使用SYS_Cn格式命名约束.

创建约束:

在建表的同时创建

建表后创建

可定义列级或表级约束.

可通过数据字典表查看约束.

建表时创建约束

create table OTL_NICOTINE_GRADE ( ID NUMBER not null, SEASON_NO NUMBER(4) not null, RECEIPT_NO NUMBER(8) not null,

GRADE VARCHAR2(10) not null, PROPORTION NUMBER(5,2) not null, WEIGHT NUMBER(10,2) not null, VALUE NUMBER(12,2) not

null, constraint PK_OTL_NICOTINE_GRADE primary key (ID, GRADE), constraint FK_OTL_NICO_REFERENCE_OTL_CHEC foreign

key (ID) references OTL_CHECK_CHEM (ID) )

建表后添加约束

ALTER TABLE (table_name) ADD ( CONSTRAINT (foreign key constraint name) FOREIGN KEY (field name ) REFERENCES

primary_table_name ( primary_table_primary_index_field )

SQL> create table emplyees(

2 employee_id number(6),

3 last_name varchar2(25) not null,

4 salary number(8,2),

5 commission_pct number(2,2),

6 hire_date date,

7 constraint emp_hire_date_1 not null,

8 CONSTRAINT dept_dname_uk UNIQUE(emp_name)

9 );

表已创建。

SQL>

1 select constraint_name,table_name

2 from dba_constraints

3 where table_name='EMPLYEES'

CONSTRAINT_NAME TABLE_NAME

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

SYS_C003012 EMPLYEES

EMP_HIRE_DATE_1 EMPLYEES

定义主键约束PRIMARY KEY

SQL> CREATE TABLE dept(

2 deptno NUMBER(2),

3 dname VARCHAR2(14),

4 loc VARCHAR2(13),

5 CONSTRAINT dept_dname_uk UNIQUE(dname),

6 CONSTRAINT dept_deptno_pk PRIMARY KEY(deptno));

外键约束FOREIGN KEY

SQL> CREATE TABLE emp(

2 empno NUMBER(4),

3 ename VARCHAR2(10) NOT NULL,

4 job VARCHAR2(9),

5 mgr NUMBER(4),

6 hiredate DATE,

7 sal NUMBER(7,2),

8 comm NUMBER(7,2),

9 deptno NUMBER(7,2) NOT NULL,

10 CONSTRAINT emp_deptno_fk FOREIGN KEY (deptno)

11 REFERENCES dept (deptno));

外键约束的关键字

FOREIGN KEY

定义子表的哪一列作为外键约束

REFERENCES

指示主表和参照的列

ON DELETE CASCADE

删除主表记录时将子表相关记录删除

ON DELETE SET NULL

将外键引用置为空值

CHECK 约束

定义每一记录都要满足的条件

条件表达式不允许有:

CURRVAL, NEXTVAL, LEVEL, ROWNUM

SYSDATE, UID, USER, USERENV 函数

参照其他记录的值

..., deptno NUMBER(2),

CONSTRAINT emp_deptno_ck

CHECK (DEPTNO BETWEEN 10 AND 99),...

加约束

ALTER TABLE table

ADD [CONSTRAINT constraint] type (column);

可加或删除约束，但不能修改

可使约束生效和失效

使用MODIFY子句可加 NOT NULL约束

加 FOREIGN KEY 约束到EMP表

SQL> ALTER TABLE emp

2 ADD CONSTRAINT emp_mgr_fk

3 FOREIGN KEY(mgr) REFERENCES emp(empno);

删除约束

删除约束emp_mgr_fk

SQL> ALTER TABLE emp

2 DROP CONSTRAINT emp_mgr_fk;

删除主键约束和相关的外键约束

SQL> ALTER TABLE dept

2 DROP PRIMARY KEY CASCADE;

使约束失效

在ALTER TABLE 语句中执行DISABLE子句可使完整性约束失效

使用 CASCADE 选项可使依赖的完整约束失效

SQL> ALTER TABLE emp

2 DISABLE CONSTRAINT emp_empno_pk CASCADE;

使用ENABLE子句将失效的约束生效

SQL> ALTER TABLE emp

2 ENABLE CONSTRAINT emp_empno_pk;

当使UNIQUE 或 PRIMARY KEY约束生效时,会自动创建 UNIQUE 或PRIMARY KEY 索引.

延迟约束验证

ALTER TABLE AAA ADD (CONSTRAINT AAA_PK PRIMARY KEY(a) DEFERRABLE) ;

ALTER TABLE BBB

ADD (CONSTRAINT BBB_FK FOREIGN KEY(a)

REFERENCES AAA(a)

ON DELETE CASCADE DEFERRABLE)

CREATE OR REPLACE TRIGGER ID_TRG AFTER UPDATE ON AAA FOR EACH ROW

BEGIN

UPDATE BBB SET a=:NEW.a WHERE a=:OLD.a;

END;

查看约束

通过查看 USER_CONSTRAINTS 表可得到用户的所有约束.

SQL> SELECT constraint_name, constraint_type,

2 sarch_condition

3 FROM user_constraints

4 WHERE table_name = 'EMP';

CONSTRAINT_NAME C SEARCH_CONDITION

SYS_C00674 C EMPNO IS NOT NULL

SYS_C00675 C DEPTNO IS NOT NULL

EMP_EMPNO_PK P

...

查看约束建立在哪些列

通过查询USER_CONS_COLUMNS 视图可获得约束建立在哪些列上

SQL> SELECT constraint_name, column_name

2 FROM user_cons_columns

3 WHERE table_name = 'EMP';

CONSTRAINT_NAME COLUMN_NAME

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

EMP_DEPTNO_FK DEPTNO

EMP_EMPNO_PK EMPNO

EMP_MGR_FK MGR

SYS_C00674 EMPNO

SYS_C00675 DEPTNO

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

constraint_type

约束的类型有如下几种：

C (check constraint on a table)

P (primary key)

U (unique key)

R (Referential AKA Foreign Key)

V (with check option, on a view)

O (with read only, on a view)

【编辑推荐】

【责任编辑：迎迎 TEL：(010)68476606】