Oracle约束(1)(2)

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)