【SQL】约束

1. 添加约束  

1）使用ALTER TABLE语句

•添加或删除约束，不会修改其结构

•启用和禁用约束

•通过使用MODIFY子句添加NOTNULL约束

ALTER TABLE <table_name> ADD [CONSTRAINT <constraint_name>]type (<column_name>); 

例如： 

alter table emp modify empno primary key;  

alter table empadd constraint fk_deptno foreign key(deptno) references dept(deptno); 

2）ON DELETE子句

•使用ON DELETE CASCADE子句级联删除 

alter table empadd constraint fk_deptno foreign key (deptno)references dept(deptno) on delete cascade; 

•使用ON DELETE SET NULL当父节点记录删除时子节点记录置空

alter table empadd constraint fk_deptno foreign key (deptno) references dept(deptno)on delete set null; 

2. 延迟约束延迟约束有以下属性

•DEFERRABLE（延迟）或NOT  DEFERRABLE（不延迟）

•INITIALLY  DEFERRED（事务结束后验证）或INITIALLY  IMMEDIATE（语句执行时立即验证）

1)创建约束时添加 

ALTER TABLE dept ADD CONSTRAINT dept_id_pk PRIMARY KEY (department_id) DEFERRABLE INITIALLY DEFERRED; 

2)改变指定约束的属性   

SET CONSTRAINT dept_id_pk IMMEDIATE

3)改变会话级别的约束   

ALTER SESSION SET CONSTRAINTS=IMMEDIATE    

CREATE TABLE emp_new_sal (salary NUMBER CONSTRAINT sal_ck CHECK (salary > 100) DEFERRABLE INITIALLY IMMEDIATE,bonus NUMBER CONSTRAINT bonus_ck CHECK (bonus > 0 )DEFERRABLE INITIALLY DEFERRED );

3.删除约束

•删除emp表的 fk_deptno约束 

alter table emp drop constraint fk_deptno ; 

•删除dept表的PRIMARY KEY 约束并删除相关联的FOREIGN KEY约束

ALTER TABLE dept DROP PRIMARY KEY CASCADE; 

4.禁用约束

•使用ALTERTABLE 语句的DISABLE子句禁用完整性约束。

•应用CASCADE选项禁用相关的完整性约束。 

ALTER TABLE emp2 DISABLE CONSTRAINT emp_dt_fk; 

5.启用约束

•使用ENABLE 子句激活当前禁用表中定义的完整性约束。 

ALTER TABLE emp2 ENABLE CONSTRAINT emp_dt_fk; 

6.级联约束

•CASCADE CONSTRAINTS 子句在DROP COLUMN 子句中使用。

•删除字段CASCADE CONSTRAINTS 指定子句，同时也将删除所有定义的多列约束。

•删除列上定义的PRIMARY和UNIQUE约束时指定CASCADE CONSTRAINTS 子句，则将删除所有引用它的完整性约束。

示例： ALTER TABLE emp2DROP COLUMN employee_id CASCADE CONSTRAINTS;  

ALTER TABLE test1 DROP (col1_pk, col2_fk, col1) CASCADE CONSTRAINTS; 

7.Cascade

•删除用户的时候级联删除对象 drop user test cascade; 

•删表时级联删除约束 drop table test cascade constraints; 

•删字段的时候，级联删除约束(pk,fk,.c) alter table test drop column (id) cascade constraints;  alter table test disable constraint pk_test cascade;  

create table child (c1 number primary key,c2 number references parent(c1) on delete cascade); 

8.重命名表的列和约束

•使用RENAME COLUMN子句的ALTER TABLE语句来重新命名表列。 

ALTER TABLE marketing RENAME COLUMN team_idTO id; 

•使用RENAME CONSTRAINT子句的ALTER TABLE语句来重命名表中任意已存在的约束。 

ALTER TABLE marketing RENAME CONSTRAINT mktg_pkTO new_mktg_pk; 

9.查看表上有什么约束

SQL> desc user_constraints;  Name                               Null?    Type  ----------------------------------------------------- -------- ------------------------------------  OWNER                                VARCHAR2(30)  CONSTRAINT_NAME                       NOT NULL VARCHAR2(30)  CONSTRAINT_TYPE                        VARCHAR2(1)  TABLE_NAME                           NOT NULL VARCHAR2(30)  SEARCH_CONDITION                        LONG  R_OWNER                            VARCHAR2(30)  R_CONSTRAINT_NAME                        VARCHAR2(30)  DELETE_RULE                            VARCHAR2(9)  STATUS                             VARCHAR2(8)  DEFERRABLE                            VARCHAR2(14)  DEFERRED                            VARCHAR2(9)  VALIDATED                            VARCHAR2(13)  GENERATED                            VARCHAR2(14)  BAD                                VARCHAR2(3)  RELY                                VARCHAR2(4)  LAST_CHANGE                            DATE  INDEX_OWNER                            VARCHAR2(30)  INDEX_NAME                            VARCHAR2(30)  INVALID                            VARCHAR2(7)  VIEW_RELATED                            VARCHAR2(14)

##查看EMPOYEES 表上面的约束

SQL> SELECT CONSTRAINT_NAME,CONSTRAINT_TYPE FROM USER_CONSTRAINTS WHERE TABLE_NAME='EMPLOYEES'; CONSTRAINT_NAME                C ------------------------------ - EMP_SALARY_MIN                 C EMP_EMAIL_UK                   U EMP_EMP_ID_PK                  P EMP_DEPT_FK                    R EMP_JOB_FK                     R EMP_MANAGER_FK                 R EMP_LAST_NAME_NN               C EMP_EMAIL_NN                   C EMP_HIRE_DATE_NN               C EMP_JOB_NN                     C 10 rows selected.

## 约束类型字母代表含义

Type CodeType DescriptionActs On LevelCCheck on a tableColumnORead Only on a viewObjectPPrimary KeyObjectRReferential AKA Foreign KeyColumnUUnique KeyColumnVCheck Option on a viewObject