Oracle12C--约束的其他操作（十）

知识点的梳理：

1. 在建立数据表的时候要同时建立好相应的约束，不应先创建表，使用了一段时间后在创建约束，这样不利于数据的维护；

2. 增加约束语法：（在增加约束时，一定要保证表中的数据不能违反增加的约束alter table 表名称 add constraint 约束名称 约束类型(约束字段);

3. 禁用约束语法： [cascade]该可选项是关闭存在有完整性关系的约束（主要是外键）:alter table 表名称 disable constraint 约束名称 [cascade];

4. 删除约束语法：删除约束依靠的是约束的名称：alter table 表名称 drop constraint 约束名称 [cascade];

查看约束

由于 Oracle 中所有的数据对象都是通过数据字典表进行记录的，所以，用户设置的约束也都在数据字典中被保存；

使用 user_constraints ，查看全部的约束名称、类型、约束设置对应的表名称

SELECT constraint_name,constraint_type,table_name FROM user_constraints ;

提问：constraint_type表示什么含义？该字段下的字母又具有什么意思？
constraint_type表示的约束类型，这些字母则是每个约束类型的简写：primary key（P）,foreign key（R），check（C）,not null（C）,unique（Q）

1. 
   

查询指定表的全部约束

SELECT constraint_name,constraint_type,table_name 
FROM user_constraints WHERE table_name='EMP' ;

user_constraints 只能查询哪张表存在何种约束名称，而 user_cons_columns 可以查询哪个列上存在约束

通过 user_cons_columns 数据字典，查看哪些表中的哪些列具有约束

SELECT * FROM user_cons_columns ;

增加约束

创建一张示例表：

DROP TABLE member purge ;
CREATE TABLE member(
	mid        NUMBER ,
	name        VARCHAR2(30)  ,
	age        NUMBER
) ;

增加约束示例：

为 member 表的 mid 字段增加主键约束：

ALTER TABLE member ADD CONSTRAINT pk_midd PRIMARY KEY(mid) ;

为member表的age增加检查约束 ：

ALTER TABLE member ADD CONSTRAINT ck_age CHECK(age BETWEEN 0 AND 200) ;

查看member表中的约束

SELECT constraint_name,constraint_type,table_name 
FROM user_constraints WHERE table_name='MEMBER' ;

增加“非空约束”

增加非空约束不能使用上面的方式；

为 name 字段设置非空约束

ALTER TABLE member MODIFY (name VARCHAR2(30) NOT NULL) ;

启用/禁用约束

禁用约束： 使用过多的约束会降低 Oracle 的性能，可以禁用不常用的约束；

示例 sql:

DROP TABLE advice PURGE ;
DROP TABLE member PURGE ;
CREATE TABLE member (
    mid       NUMBER    ,
    name      VARCHAR2(200)   NOT NULL ,
    CONSTRAINT pk_mid PRIMARY KEY (mid) 
) ;
CREATE TABLE advice (
    adid      NUMBER ,
    content   CLOB            NOT NULL ,
    mid       NUMBER ,
    CONSTRAINT pk_adid PRIMARY KEY (adid) ,
    CONSTRAINT fk_mid FOREIGN KEY(mid) REFERENCES member(mid) ON DELETE SET NULL
) ;
INSERT INTO member (mid,name) VALUES (1,'hey') ; 
INSERT INTO member (mid,name) VALUES (2,'yan') ;
INSERT INTO advice (adid,content,mid) VALUES (1,'应该提倡内部沟通机制，设置总裁邮箱',1) ;
INSERT INTO advice (adid,content,mid) VALUES (2,'为了使公司内部良性发展，所有的部门领导应该重新应聘上岗',1) ;
INSERT INTO advice (adid,content,mid) VALUES (3,'要多开展员工培训活动，让员工更加有归属感',1) ;
INSERT INTO advice (adid,content,mid) VALUES (4,'应该开展多元化业务，更加满足市场需求',2) ;
INSERT INTO advice (adid,content,mid) VALUES (5,'大力发展技术部门，为本公司设计自己的ERP系统，适应电子化信息发展要求',2) ;
COMMIT ;

禁用 advice 表中的 adid 主键约束“ pk_adid ”， pk_adid 约束没有设置任何外键，所以可以立刻被禁用

ALTER TABLE advice DISABLE CONSTRAINT pk_adid ;

禁用member表中的“pk_mid”约束，此字段在advice表中是外键

ALTER TABLE member DISABLE CONSTRAINT pk_mid ;

由于 member 表的 mid 字段作为 advice 表的外键使用，所以此时执行禁用约束会出现如下异常

可以在此sql后面增加cascade来解决

ALTER TABLE member DISABLE CONSTRAINT pk_mid cascade;

启用约束：

重新启动member表中的主键约束“pk_mid”

ALTER TABLE member ENABLE CONSTRAINT pk_midd ;

范例：重新启动advice表中的主键约束“pk_adid”

ALTER TABLE advice ENABLE CONSTRAINT pk_adid ;

删除约束

删除 advice 表之中的“ pk_adid ”约束 —— 无关联外键

ALTER TABLE advice DROP CONSTRAINT pk_adid ;

--删除member表之中的“pk_mid”约束 —— 有关联外键，需要cascade的帮助
ALTER TABLE member DROP CONSTRAINT pk_mid CASCADE ;