【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 Code | Type Description | Acts On Level |
| C | Check on a table | Column |
| O | Read Only on a view | Object |
| P | Primary Key | Object |
| R | Referential AKA Foreign Key | Column |
| U | Unique Key | Column |
| V | Check Option on a view | Object |
