高质量数据库建模系列课程<9> PPT & 讲义 -- 约束

浏览: 3110

时光荏苒,本课已经出了三月有余,到今天(2016-4-21)为止,一共有1147名观众,非常感谢大家能有耐心听我的课。由于最近工作比较忙,所以出课的进度缓慢,在此表示深深的歉意。

课程地址:http://www.hellobi.com/course/54

Clipboard Image.png

此外,希望大家听完课以后去https://www.surveymonkey.com/r/CYQLCTD 填写一下调查问卷,我也好有的放矢,对于我教的不好的地方做调整和改善。




本节课我们讲的是的数据库约束的定义以及应用。


大家好, 这节课我们介绍一下约束(Constraint)的定义以及使用. 从广义上来讲, 约束其实就是商业规则在数据库中的体现. 而真实世界的里面的商业规则其实非常多,并且变化万千,数据库世界里也只是能尽可能的体现其中一小部分。那么有哪些呢?

首先,是唯一标识

这个在数据库里面真是至关重要,我们大家熟知的主键,其实就是这种约束。除了主键以外呢,还有Unique Index也是另外一种唯一标识。

此外还有我们前面曾经讲过的非空约束,默认值,检查以及参照完整性等内容.



首先我们来看唯一标识。唯一标识在真实世界里的意义就是能够唯一确定实体某一个实例的属性或者属性组,最常见的就是我们前面讲过的主键。在PowerDesigner里面的逻辑模型呢,有Identifier的选项,在这里面我们可以去创建Unique Identifier。除了主键是唯一的以外,我们可能还有这种需求,实体中某个属性或者属性组,我也希望是唯一的,尽管它不是主键。比如一个员工表,员工ID是主键,但我也希望员工的Email地址也是唯一,这种情况怎么办呢,就建一个Identifier,我们来操作一下。我这边再说一遍哈,因为约束各个数据库产品语法差异是很大的,我这个课里面都是postgreSQL为例的,当你选用你所用的数据库的时候语法上可能会有很大不同,不过其实内容上是相似的。

类似这种使用的很多,在我们数据仓库建设里面非常常见还有维度表里面主键是代理键,自然键其实也是唯一的,我会建一个Unique Check给自然键,以确保数据不会出现错误。



那么生成的DDL是什么样子呢? 这里我们还是以postgreSQL数据库为例。首先看主键的DDL是什么样子,这里的第一个DDL,关键词是constraint,然后给主键起个名字,一般都是以PK作为前缀,我们这里用PK_EMP作为这个主键的名称,然后关键词Primary Key,括号(EMP_ID),这个就表示用EMP_ID作为表EMP的主键。

那么我们再来看,除了主键以外呢,我们还可以创建另外一种唯一标识约束。看第二个例子,我画红框的部分,还是关键词constraint,然后加一个名字,这里呢,我们把它称为可选键,AlternativeKey,所以我们用Alternative Key的缩写AK作为前缀起个名字。然后关键词UNIQUE,后面加上EMP_EMAIL_ADDRESS这个字段,就表示EMP_EMAIL_ADDRESS员工的电子邮件是唯一的。这里需要注意的是,EMP_EMAIL_ADDRESS这个字段,是可以为空的,而主键是不可以为空的。

除了用上面这种方式以外呢,还可以有一种创建唯一标识的方法,就是创建唯一索引,我下面给的例子CREATE UNIQUE INDEX。

好,那我们到数据库里面试验一下,看看是怎样的效果。

首先我们建一张表EMP, EMP_ID作为主键.

先插入一条EMP_ID为000001的数据,成功。

再插入一条EMP_ID为000001的数据,出错了。

 

再看一下unique constraint,这回我们用EMP_EMAIL_ADDRESS作为可选键。创建CONSTRAINT。

然后插入一条数据,EMP_ID为000001,email为raymond@outlook.com成功,再插入一条EMP_ID为000002的,显然这个主键不冲突,但这条数据的EMAIL也是raymond@outlook.com,大家看看,出错了。

这里需要注意一下,EMP_EMAIL_ADDRESS这个COLUMN,我设置成可为空了,那我们插入一条NULL值试一下,成功了。里面已经有一条数据有NULL值,那再插一条会不会成功?我们再试一下,又成功,也就是说如果是NULL值的话,怎么插入都无所谓,不会出现冲突。

那我们在看UNIQUE INDEX,同样的重新建表。

插入一条数据,成功,同样的例子,再插入一条email一样的,失败。再插一个email为null的,成功;再插一个为Null的,还是成功。也就是说unique index在效果上和constrain unique是一样,但实现方式是不一样的,这个还是另外建了索引的,除了保证数据的唯一性以外,能够提高查询的性能。这里再强调一下,CONSTRAINT UNIQUE和UNIQUE INDEX,对应的字段都是可以为空的,而主键是不可以为空的。

一、Unique Indentifier
1. 主键
CREATE TABLE EMP (
EMP_ID CHAR(6) NOT NULL,
EMP_NAME VARCHAR(50) NOT NULL,
EMP_EMAIL_ADDRESS VARCHAR(50) NULL,
CONSTRAINT PK_EMP PRIMARY KEY (EMP_ID)
);
insert into EMP(EMP_ID, EMP_NAME, EMP_EMAIL_ADDRESS) values ('000001','Raymond', 'raymond@outlook.com' );
insert into EMP(EMP_ID, EMP_NAME, EMP_EMAIL_ADDRESS) values ('000001','Raymond2', 'raymond2@outlook.com' );
出现主键冲突


2. Unique constraint
drop table EMP ;
CREATE TABLE EMP (
EMP_ID CHAR(6) NOT NULL,
EMP_NAME VARCHAR(50) NOT NULL,
EMP_EMAIL_ADDRESS VARCHAR(50) NULL,
CONSTRAINT PK_EMP PRIMARY KEY (EMP_ID),
CONSTRAINT AK_IDENTIFIER_EMAIL_EMP UNIQUE (EMP_EMAIL_ADDRESS)
);
insert into EMP(EMP_ID, EMP_NAME, EMP_EMAIL_ADDRESS) values ('000001','Raymond', 'raymond@outlook.com' );
insert into EMP(EMP_ID, EMP_NAME, EMP_EMAIL_ADDRESS) values ('000002','Raymond2', 'raymond@outlook.com' );
insert into EMP(EMP_ID, EMP_NAME, EMP_EMAIL_ADDRESS) values ('000003','Raymond', null );
insert into EMP(EMP_ID, EMP_NAME, EMP_EMAIL_ADDRESS) values ('000004','Raymond', null );

3. Unique index
DROP TABLE EMP;
CREATE TABLE EMP (
EMP_ID CHAR(6) NOT NULL,
EMP_NAME VARCHAR(50) NOT NULL,
EMP_EMAIL_ADDRESS VARCHAR(50) NULL,
CONSTRAINT PK_EMP PRIMARY KEY (EMP_ID)
);

CREATE UNIQUE INDEX UI_EMP ON EMP (
EMP_EMAIL_ADDRESS
);

insert into EMP(EMP_ID, EMP_NAME, EMP_EMAIL_ADDRESS) values ('000001','Raymond', 'raymond@outlook.com' );
insert into EMP(EMP_ID, EMP_NAME, EMP_EMAIL_ADDRESS) values ('000002','Raymond2', 'raymond@outlook.com' );
insert into EMP(EMP_ID, EMP_NAME, EMP_EMAIL_ADDRESS) values ('000003','Raymond', null );
insert into EMP(EMP_ID, EMP_NAME, EMP_EMAIL_ADDRESS) values ('000004','Raymond', null );


还有一种约束我们也经常使用,就是非空约束。这个很简单,也非常常用,看我给出的例子里面,在PowerDesigner的逻辑模型中,前面标*号的,就表示是不可为空的属性,在属性的定义里面,我们可以看到,M这个下面我打上勾的,就是不可以为空。那么生成到数据库里面呢是什么样的一个效果呢?

首先,最简单的模式,就是在创建表的时候,对字段设成NOT NULL。这样,如果我们对这个字段插入NULL值的时候,数据库就会报错并阻止你插入这种非法数据。还有一种约束,就是增加一个CHECK,如我第二个例子所示。这两种模式效果是完全一样的,但在数据库里面的实现机制是不同的。我们可以看到第一种方式不会创建单独的constraint,并且在字段的定义中明确标识该字段不可为空。而第二种方式字段的定义中可以为空,但又创建了一个新的constraint。这里引出了Constraint的CHECK,CHECK本身具备的功能更加丰富,如果你设计的字段只有NOT NULL这个约束需要做限制,那你就用NOT NULL就可以了,如果还有除了NOT  NULL以外其他的约束条件,可以采用CHECK。


这里我们还是到数据库里面看一下效果。

1. null以及默认忽略都是不可以插入的, ''是可以插入的
drop table EMP;
CREATE TABLE EMP (
EMP_ID CHAR(6) NOT NULL,
EMP_NAME VARCHAR(50) NOT NULL,
EMP_EMAIL_ADDRESS VARCHAR(50) NULL,
CONSTRAINT PK_EMP PRIMARY KEY (EMP_ID)
);
insert into EMP(EMP_ID, EMP_NAME, EMP_EMAIL_ADDRESS) values ('000001',null, 'raymond@outlook' );
insert into EMP(EMP_ID, EMP_EMAIL_ADDRESS) values ('000001','raymond@outlook' );
insert into EMP(EMP_ID, EMP_NAME, EMP_EMAIL_ADDRESS) values ('000001','','raymond@outlook' );


2. 用check实现的not null, 效果一样
drop table EMP;
CREATE TABLE EMP (
EMP_ID CHAR(6) NOT NULL,
EMP_NAME VARCHAR(50) NULL,
EMP_EMAIL_ADDRESS VARCHAR(50) NULL,
CONSTRAINT PK_EMP PRIMARY KEY (EMP_ID),
CONSTRAINT CKNONULL_EMPNAME CHECK (EMP_NAME is not null)
);
insert into EMP(EMP_ID, EMP_NAME, EMP_EMAIL_ADDRESS) values ('000001',null, 'raymond@outlook' );
insert into EMP(EMP_ID, EMP_EMAIL_ADDRESS) values ('000001','raymond@outlook' );
insert into EMP(EMP_ID, EMP_NAME, EMP_EMAIL_ADDRESS) values ('000001','','raymond@outlook' );

首先建一张表,EMP,其中EMP_NAME设置成不可为空。然后插入一条数据,EMP_NAME对应的值为NULL,显然没法插入。然后再试一个,插入时根本没有EMP_NAME column的,还是不行。最后试一个,EMP_NAME为’’长度为零的空字符串的,成功。这里再说明一下,NULL表示的是未知,空字符串不是未知,同样的对于整数和浮点数,null就是null,不能用0代替。

我们再看看用check实现的NOT NULL。重新建表,插入同样的值,先是EMP_NAME为NULL的,失败。直接忽略EMP_NAME的,不行。空字符串的,成功。可见,效果一样啊。但CHECK的功能更加丰富,我们后面再讲。你对应的字段只有NOT NULL这种要求,没必要选用CHECK的方式,性能会有一定的下降。


有关NULL值,我本来打算借这个机会多讲一些,NULL值是非常好用的用于表述我们不知道的信息,未发生的信息以及不恰当的信息,但它也带来了很多编程方面的隐患。因此无论是数据建模工程师还是未来需要使用数据库的开发人员都需要非常了解NULL值的这些开发上的隐藏的坑。以及如何采用一些办法去规避这些隐患。但这个话题太大,所以还是单独开一节课讲来的好,本节暂时先不讲。那么接下来我们来到下一个话题,默认值。默认值经常和NOT NULL配合使用,对于一些我们已知的字段进行自动的求值。比如生成订单时,自动按照currentdate获取当前日期,作为订单日期这种。还有作为审计字段,当对数据进行新增和修改时,默认的时间戳,之类。如图所示,在PowerDesigner里面,我们可以在standard check的位置设置默认值。然后我们再来看看在数据库中的操作。

CREATE TABLE EMP2 (
EMP_SK CHAR(10) NOT NULL,
EMP_ID CHAR(6) NOT NULL,
EFFECTIVE_TMS TIMESTAMP NOT NULL ,
EMP_NAME VARCHAR(50) NULL,
EMP_EMAIL_ADDRESS VARCHAR(50) NULL,
EXPIRED_TMS TIMESTAMP default '9999-12-31' NULL,
CONSTRAINT PK_EMP2 PRIMARY KEY (EMP_SK)
);

insert into EMP2(EMP_SK, EMP_ID, EFFECTIVE_TMS, EMP_NAME, EMP_EMAIL_ADDRESS)
values(1,'000001','2016-01-01','raymond', 'raymond@outlook.com');

select * from EMP2

insert into EMP2(EMP_SK, EMP_ID, EFFECTIVE_TMS, EMP_NAME, EMP_EMAIL_ADDRESS, EXPIRED_TMS)
values (2,'000001','2016-01-01','raymond', 'raymond@outlook.com', current_timestamp);

select * from EMP2

建一张新表,这个是典型的维度表结构,其中的EXPIRED_TMS,我们把它的默认值设成’9999-12-31‘。先插入第一条数据,插入的项目里直接忽略EXPIRED_TMS,看看效果如何。插入成功,EXPIRED_TMS对应的值为’9999-12-31’


前面我们提到了,constraint里面还有一些复杂的用法,CHECK。在PowerDesigner里面的standard check里,基本上可以满足我们大部分的需求。这里包括对于数值型数据的检查,枚举类型的数据检查以及字符类型的数据检查。而有些复杂的定义,不是非常适合在PowerDesigner里面来做,让我们来看几个例子,看看在PostgreSQL数据库里是怎么实现的。


drop table PRODUCT;
CREATE TABLE PRODUCT (
PRODUCT_ID INT2 NOT NULL,
PRODUCT_NAME VARCHAR(100) NULL,
NORMAL_PRICE NUMERIC(8,2) NULL DEFAULT 0
CONSTRAINT CKC_NORMAL_PRICE_PRODUCT CHECK (NORMAL_PRICE IS NULL OR (NORMAL_PRICE BETWEEN 0 AND 1000)),
DISCOUNT_PRICE NUMERIC(8,2) CHECK(DISCOUNT_PRICE<NORMAL_PRICE AND DISCOUNT_PRICE>=0),
PRODUCT_CATEGORY CHAR(1) NULL
CONSTRAINT CKC_PRODUCT_CATEGORY_EMP CHECK (PRODUCT_CATEGORY IS NULL OR (PRODUCT_CATEGORY IN ('A','B','C'))),
CONSTRAINT PK_PRODUCT PRIMARY KEY (PRODUCT_ID)
);

--normal price 超出范围的,并且PRODUCT_CATEGORY是NULL
insert into PRODUCT
(PRODUCT_ID, PRODUCT_NAME, NORMAL_PRICE, DISCOUNT_PRICE)
values
(1, 'AA', 1001.00, 888.00);

--PRODUCT_CATEGORY超出枚举范围的
insert into PRODUCT
(PRODUCT_ID, PRODUCT_NAME, NORMAL_PRICE, DISCOUNT_PRICE,PRODUCT_CATEGORY)
values
(3, 'BB', 100,99,'D');

--DISCOUNT_PRICE>NORMAL_PRICE的,也会出错
insert into PRODUCT
(PRODUCT_ID, PRODUCT_NAME, NORMAL_PRICE, DISCOUNT_PRICE,PRODUCT_CATEGORY)
values
(4, 'CC', 100,101,'B');

--再看一个复杂一点的
drop table emp3;
create table emp3(
emp_id integer not null,
emp_email varchar(100),
constraint PK_EMP3 Primary key(emp_id),
constraint ckc_emp_email check (emp_email like '_%@_%._%')
);

insert into emp3 (emp_id, emp_email) values (1, 'SSSS');
insert into emp3 (emp_id, emp_email) values (2, 'raymond2@outlook');
insert into emp3 (emp_id, emp_email) values (3, 'raymond2@outlook.com');
insert into emp3 (emp_id, emp_email) values (4, 'a@.s');

是的,check还可以支持正则表达式,感兴趣的同学可以去玩一玩


最后我们来看一下参照完整性(Referential Integrity),我们简称RI, 这个其实就是主外键关系的约束了。这里我说一下,RI在OLTP用的比较多,以及主数据管理领域,用的比较多,而在数据仓库里,用的相对比较少,但也不是不用哈。当有一些数据质量的要求时,尤其对于一些关键数据,我们需要用RI去保证数据质量。而RI约束本身会对ETL的性能有很大的影响,因此呢,如果有RI约束的话,是需要在LOAD数据之前先把RI约束删掉,然后再加载数据,最后再重新创建RI约束。

我们来看这个图,RI有几个选项。

NONE

RESTRICT

CASCADE

SETNULL

SETDEFAULT

这么五个选项,右边是对应的建表DDL样例。

这里呢,NONE和RESTRICT是很像的,比如当你在主表中去删除数据的时候,如果该数据在从表中还存在,数据库会报错。而这两个选项的区别在于,NONE是可以做延迟生效的,而RESTRICT是不可以的。关于延迟生效的细节呢,如果大家有兴趣可以去看数据库相关的书,在这我就不细讲了。然后我们看CASCADE,这个表示什么呢,当你删除或者修改主表的数据时,数据库会自动把从表里面外键相关的数据也相应的删除或者修改。比如我这个例子,产品分类和产品,如果我去删除产品的某一个分类,那么数据库就会自动把这个产品分类对应的所有产品都删除掉了。而SET NULL和SET DEFAULT是很相似的,当你修改或者删除主表的数据时,会自动把从表里对应的数据设置成NULL值或者DEFAULT值。下面我们在数据库里面简单操作一下,看看效果。

DROP TABLE PRODUCT;
DROP TABLE PRODUCT_CATEGORY;

/*==============================================================*/
/* Table: PRODUCT_CATEGORY */
/*==============================================================*/

CREATE TABLE PRODUCT_CATEGORY (
PRODUCT_CATEGORY_CD CHAR(2) NOT NULL,
PRODUCT_CATEGORY_NAME VARCHAR(100) NULL,
CONSTRAINT PK_PRODUCT_CATEGORY PRIMARY KEY (PRODUCT_CATEGORY_CD)
);

/*==============================================================*/
/* Table: PRODUCT */
/*==============================================================*/
CREATE TABLE PRODUCT (
PRODUCT_ID CHAR(6) NOT NULL,
PRODUCT_CATEGORY_CD CHAR(2) NULL default '00',
PRODUCT_NAME VARCHAR(100) NULL,
CONSTRAINT PK_PRODUCT PRIMARY KEY (PRODUCT_ID)
);

首先是NONE,因为NONE和RESTRICT除了在不能延迟生效方面以外,都一样,因此只选择了NONE这个做例子,RESTRICT的大家可以回去自己试
ALTER TABLE PRODUCT
ADD CONSTRAINT FK_PRODUCT_RELATIONS_PRODUCT_ FOREIGN KEY (PRODUCT_CATEGORY_CD)
REFERENCES PRODUCT_CATEGORY (PRODUCT_CATEGORY_CD);

insert into PRODUCT_CATEGORY(PRODUCT_CATEGORY_CD, PRODUCT_CATEGORY_NAME)
values ('01', 'AAA'),('02', 'BBB');

insert into PRODUCT(PRODUCT_ID, PRODUCT_CATEGORY_CD, PRODUCT_NAME)
values ('010000','01' ,'PRODUCT_AAA_1');
insert into PRODUCT(PRODUCT_ID, PRODUCT_CATEGORY_CD, PRODUCT_NAME)
values ('020000','03', 'PRODUCT_AAA_2'); -- 这个报错


select * from PRODUCT
select * from PRODUCT_CATEGORY

delete from product where product_category_cd='01'

--然后我们再看cascade
ALTER TABLE PRODUCT drop constraint FK_PRODUCT_RELATIONS_PRODUCT_;
ALTER TABLE PRODUCT
ADD CONSTRAINT FK_PRODUCT_RELATIONS_PRODUCT_ FOREIGN KEY (PRODUCT_CATEGORY_CD)
REFERENCES PRODUCT_CATEGORY (PRODUCT_CATEGORY_CD)
ON DELETE CASCADE ON UPDATE CASCADE;


truncate table PRODUCT;
truncate table PRODUCT_CATEGORY CASCADE;

insert into PRODUCT_CATEGORY(PRODUCT_CATEGORY_CD, PRODUCT_CATEGORY_NAME)
values ('01', 'AAA'),('02', 'BBB');


insert into PRODUCT(PRODUCT_ID, PRODUCT_CATEGORY_CD, PRODUCT_NAME)
values ('010000','01' ,'PRODUCT_AAA_1');
insert into PRODUCT(PRODUCT_ID, PRODUCT_CATEGORY_CD, PRODUCT_NAME)
values ('020000','02', 'PRODUCT_AAA_2');

select * from PRODUCT
select * from PRODUCT_CATEGORY

delete from PRODUCT_CATEGORY where PRODUCT_CATEGORY_CD='01';

--最后我们看看set null, 因为set default和set null是非常相似的,在这我就只举set null的例子
ALTER TABLE PRODUCT drop constraint FK_PRODUCT_RELATIONS_PRODUCT_;
ALTER TABLE PRODUCT
ADD CONSTRAINT FK_PRODUCT_RELATIONS_PRODUCT_ FOREIGN KEY (PRODUCT_CATEGORY_CD)
REFERENCES PRODUCT_CATEGORY (PRODUCT_CATEGORY_CD)
ON DELETE SET NULL ON UPDATE SET NULL;

insert into PRODUCT_CATEGORY(PRODUCT_CATEGORY_CD, PRODUCT_CATEGORY_NAME)
values ('01', 'AAA'),('02', 'BBB');


insert into PRODUCT(PRODUCT_ID, PRODUCT_CATEGORY_CD, PRODUCT_NAME)
values ('010000','01' ,'PRODUCT_AAA_1');
insert into PRODUCT(PRODUCT_ID, PRODUCT_CATEGORY_CD, PRODUCT_NAME)
values ('020000','03', 'PRODUCT_AAA_2');

select * from PRODUCT
select * from PRODUCT_CATEGORY

delete from PRODUCT_CATEGORY where PRODUCT_CATEGORY_CD='01'

首先是NONE和RESTRICT除了延迟生效,其他都一样,我这里就只以NONE为例. RESTRICT以及生效延迟大家如果有兴趣可以课后自己试一试。

建表,创建CONSTRAINT

插入数据,查询一下看看效果。

删除PRODUCT中的数据,看看怎样的效果。

再删除PRODUCT_CATEGORY里的数据,看看效果。

大家可以看到当有主外键关联关系的时候,删除主表的数据时,会报错的。

然后我们再看看CASCADE

删除原来的CONSTRAINT,重建一个CASCADE的CONSTRAINT。

清空数据,插入数据,看一下

然后删除父表中PRODUCT_CATEGORY_CD为01的数据,我们来看看子表PRODUCT有什么变化。

看到没有,PRODUCT_CATEGORY_CD为01的被自动删掉了,这个就是用CASCADE模式处理的办法。我这里举的例子是删除,同样的,如果你把父表的PRODUCT_CATEGORY_CD为01的数据改成03,那么子表PRODUCT对应的PRODUCT_CATEGORY_CD也会自动的由01变成03.

最后我们看一下setnull,因为set default和set null是非常相似的,所以这里我只举set null的例子,set default大家可以回去自己试。

推荐 7
本文由 BAO胖子 创作,采用 知识共享署名-相同方式共享 3.0 中国大陆许可协议 进行许可。
转载、引用前需联系作者,并署名作者且注明文章出处。
本站文章版权归原作者及原出处所有 。内容为作者个人观点, 并不代表本站赞同其观点和对其真实性负责。本站是一个个人学习交流的平台,并不用于任何商业目的,如果有任何问题,请及时联系我们,我们将根据著作权人的要求,立即更正或者删除有关内容。本站拥有对此声明的最终解释权。

3 个评论

没事,等着老师
ALTER TABLE [dbo].[DimReseller] WITH CHECK ADD CONSTRAINT [FK_DimReseller_DimGeography] FOREIGN KEY([GeographyKey])
REFERENCES [dbo].[DimGeography] ([GeographyKey])
GO

那么这么说默认来讲的主外键RI就是None型的了?

我在SQL SERVER下测试如下两种ok
ON DELETE set null ON UPDATE set null;
On Delete Cascade on update Cascade;

但是测试Restrict不支持,不知道是否数据库原因?
去查手册

要回复文章请先登录注册