Oracle12C--数据约束类型（八）

Oracle约束属于数据库的一种对象

Oracle为了方便对约束进行维护，把所有的约束都作为独立的数据库对象进行保存，这些信息也都保存在了数据字典中，所以每个约束都需要一个自己的名词才可以进行维护；

当用户没有为约束设置名字的时候会自动由系统分配一个名字，系统会自动分配一个约束名称，例如：

c0010201就是系统分配的；

在建表的时候，可以通过constraint为约束指定一个名称；

非空约束（NK）

1. 解析：如果某个字段不能null，且必须存在数据，可以使用非空约束；关键字：not null
   示例1：定义member表，为name字段设置非空约束

   DROP TABLE member PURGE ;
   CREATE TABLE member(
       mid     NUMBER   ,
       name    VARCHAR2(200)   NOT NULL 
   ) ;

   示例2：向member表中增加正确的数据，可以插入

   INSERT INTO member(mid,name) VALUES (1,'hey') ;

   
   示例3：向member表中增加错误的数据（两种语句的执行结果一样）

   --明确设置name字段为null：	

   INSERT INTO member(mid,name) VALUES (3,null) ;
   --不设置name字段的内容：	

   INSERT INTO member(mid) VALUES (3) ;
   

   
   违反了非空约束，错误信息会提示“用户名”，“表名称”，“字段名称”等明确信息，通知用户错误出现的位置

唯一约束

解析：不允许表中的数据出现重复，关键字unique

注意：唯一约束对null值无效，也就是说null值可以重复；

候补码（候补键）：当某个属性或属性组的值可以唯一标识唯一一行元组时，该属性或属性组就是候补码

示例 1 ：创建 member 表，在 email 字段上设置唯一约束

DROP TABLE member PURGE ;
CREATE TABLE member(
    mid     NUMBER   ,
    name    VARCHAR2(200)   NOT NULL ,
    email   VARCHAR2(50)    UNIQUE
) ;

错误提示：如果插入了重复数据，提示并不会标识出具体位置

原因分析：这种情况是因为现在的程序没有为约束指定一个具体的名称

示例2：为唯一约束指定一个名字

建议名称格式为：“约束简写_字段”

DROP TABLE member PURGE ;
CREATE TABLE member(
    mid     NUMBER   ,
    name    VARCHAR2(200)    NOT NULL ,
    email   VARCHAR2(50)  ,
    CONSTRAINT uk_email      UNIQUE (email)
) ;

错误信息改变为：

主键约束

主键约束 = 非空约束 + 唯一约束，关键字：primary key

示例1：设置member表中的mid为主键

DROP TABLE member PURGE ;
CREATE TABLE member(
    mid     NUMBER          PRIMARY KEY ,
    name    VARCHAR2(200)   NOT NULL ,
    email   VARCHAR2(50)  ,
    CONSTRAINT uk_email UNIQUE (email)
) ;

错误提示：插入的数据为 Null 时的提示

错误提示：插入的数据重复时的提示

示例 2 ：也可以使用 constraint 来设置主键约束的名称

DROP TABLE member PURGE ;
CREATE TABLE member(
    mid     NUMBER       ,
    name    VARCHAR2(200)   NOT NULL ,
    email   VARCHAR2(50)  ,
    CONSTRAINT pk_mid PRIMARY KEY (mid) ,
    CONSTRAINT uk_email UNIQUE (email)
) ;

示例 3 ：设置多个主键，这样的主键称为复合主键，只要这些字段的内容完全一样的时候才会违反约束

注意：尽量不要使用复合主键

DROP TABLE member PURGE ;
CREATE TABLE member(
    mid     NUMBER  ,
    name    VARCHAR2(200)   NOT NULL ,
    email   VARCHAR2(50) ,
    CONSTRAINT pk_mid_name PRIMARY KEY (mid,name) ,
    CONSTRAINT uk_email UNIQUE (email)
) ;

检查约束

对数据增加的条件进行过滤，表中的每行数据都必须满足指定的过滤条件。

关键字： check

示例1：在member表中增加age字段（年龄范围是0~200岁）和sex字段（只能是男或女）

DROP TABLE member PURGE ;
CREATE TABLE member(
    mid     NUMBER       ,
    name    VARCHAR2(200)   NOT NULL ,
    email   VARCHAR2(50)  ,
    age     NUMBER          CHECK (age BETWEEN 0 AND 200) ,--约束1
    sex     VARCHAR2(10) ,
    CONSTRAINT pk_mid_name PRIMARY KEY (mid,name) ,
    CONSTRAINT uk_email UNIQUE (email) ,
    CONSTRAINT ck_sex   CHECK (sex IN ('男','女'))--约束2
) ;

通过这两条约束语句，可以将年龄限制在 0~200 之间，性别只能为男或者女；

主外键约束

主外键约束是子表的某一个字段的内容取值范围必须由主表指定，通过外键将两张数据表联系起来；

子表与父表关联的外键字段，该字段在父表中必须是主键约束或唯一约束；

关键字： foreign key （简称 FK ）

为什么要使用主外键约束：

例如：现在公司要求每一个成员为公司发展提出一些更好的建议，并且希望将这些建议保存在数据表中，那么根据这样的需求，可设计出如下的图：

两张表的作用如下：

人员表：用于保存成员的基本信息（编号，姓名）；

建议表：保存每一个成员提出的建议内容，所以在该表中保存了一个成员编号，即通过此成员编号可以和成员表进行数据的关联；

根据上图设计出如下 sql 语句：在建议表中增加一列，用于表示意见所属的会员是哪一位，会员表和意见表通过 mid 字段进行关联；

DROP TABLE member PURGE ;
DROP TABLE advice 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)
) ;

插入正确的数据 —— 向member表插入两个会员信息

INSERT INTO member (mid,name) VALUES (1,'hey') ; 
INSERT INTO member (mid,name) VALUES (2,'yan') ;
COMMIT ;

插入正确的数据 —— 向advice表插入五条新记录

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 ;

数据插入完毕后，我们要通过一个数据表的复杂查询来验证以上的数据是否有效：

SELECT m.mid,m.name,COUNT(a.mid)
FROM member m,advice a 
WHERE m.mid=a.mid 
GROUP BY m.mid,m.name ;

建议都是由成员提出的，那么在建议表(advice)中的mid的取值范围应该由成员表(member)中的mid字段的内容来决定，即在advice表中设置的mid，一定是在member表中mid列上所存在的内容。但此时所设置的表关系中，并没有此限制，所以建议表是可以插入错误的数据的，即会员表中不存在mid的记录；

INSERT INTO advice (adid,content,mid) VALUES (6,'岗位职责透明化',99) ;

在会员表中，是没有99这个字段值的；为了解决这个问题，就需要主外键约束；

上例添加主外键约束：

改表结构，指定主-外键约束：通过foreign key设置了一个外键约束（mid）

DROP TABLE member PURGE ;
DROP TABLE advice 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)
) ;

主外键约束简写：
如果编写外键约束时不需要指定约束的名称，可简写为：
mid number referencesmember(mid),

向advice表中插入错误的数据—— 此时member表中没有mid=99的数据

INSERT INTO advice (adid,content,mid) VALUES (6,'岗位职责透明化',99) ;

使用正确的数据 插入

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 (3,'要多开展员工培训活动，让员工更加有归属感',2) ;
COMMIT ;