Chapter 7 Integrity(完整性), Views(视图), Security(安全性), and Catalogs(目录)

约束

图片 1 

主关键字约束(Primary Key Constraint)

用来指定表中的一列或几列组合的值在表中具有唯一性。建立主键的目的是让外键来引用。

from Database Design to Physical Form

Primary Key的创建方式

在创建表时创建Primary Key

CREATE TABLE table1(
    t_id VARCHAR(12) ,
    t_name VARCHAR(20),
    t_phone VARCHAR(20),
    CONSTRAINT t_idss PRIMARY KEY(t_id)
);

对t_id列创建主键,约束名为t_idss。

CREATE TABLE

删除Primary Key
ALTER TABLE table1
DROP CONSTRAINT t_idss;

约束名与列名不一致,此处填写约束名

integrity constraints (完整性约束)

向已有表中添加Primary Key
ALTER TABLE table1
ADD CONSTRAINT t_idss
PRIMARY KEY(t_id);

CREATE VIEW

添加Primary Key的另一种示例
ALTER TABLE Products
ADD PRIMARY KEY(prod_id);

虽然上述代码运行没问题,查看表格设计也可以看到Primary
Key设置成功,但是在删除Primary Key操作时会提示:
消息3728,级别16,状态1,第1 行
‘prod_id’ 不是约束。
消息3727,级别16,状态0,第1 行
未能删除约束。请参阅前面的错误信息。
原因是添加Primary
Key语句中没有用CONSTRAINT指明约束名,系统自动生成了主键名和约束名,要先查看主键名和约束名,删除时填写的也是约束名。
这种情况的正确删除方法

ALTER TABLE Products
DROP CONSTRAINT CK__Products__prod_p__1A14E395;
ALTER TABLE Products
DROP CONSTRAINT PK__Products__56958AB222AA2996;

Security

多列组合添加主键约束
CREATE TABLE table1(
    t_id VARCHAR(12),
    s_id VARCHAR(20),
    score FLOAT,
    CONSTRAINT ts_id PRIMARY KEY(t_id,s_id)
);

The GRANT & REVOKE statements

外关键字约束(Foreign Key Constraint)

定义了表之间的关系,用来维护两个表之间的一致性的关系。
在创建表时创建Foreign Key Constraint

CREATE TABLE table2(
    s_id VARCHAR(20),
    s_name VARCHAR(12),
    s_tellphone VARCHAR(11),
    s_address VARCHAR(20),
    CONSTRAINT PK_s_id PRIMARY KEY(s_id),
);--首先新建table2,设置s_id为主键

CREATE TABLE table1(
    t_id VARCHAR(12),
    s_id VARCHAR(20),
    score FLOAT,
    CONSTRAINT pk_ts_id PRIMARY KEY(t_id,s_id),--新建table1,对t_id和s_id设置联合主键,键名pk_ts_id
    CONSTRAINT fk_s_id FOREIGN KEY(s_id)--对s_id设置外键fk_s_id
    REFERENCES table2(s_id)--外键fk_s_id外键关联table2的列s_id
    ON DELETE CASCADE--设置在table1的s_id删除时table2的s_id同时删除
    ON UPDATE CASCADE--设置在table1的s_id更新时table2的s_id同时更新
);

注:对table1设置外键关联table2,在插入数据时需要先插入table2的数据,才能成功插入table1的数据。更改table2.s_id数据,table1.s_id数据也会自动改变。但是更改table1.s_id数据,执行时报外键冲突。总之对table1设置外键关联table2后,table1的数据跟着table2走,不能反着来。

添加和删除外键约束同主键。

Catalogs

UNIQUE约束

除主键外另一种可以定义唯一约束的类型,允许空值。添加UNIQUE的方法同上,这里只简单举例。

USE test
GO
ALTER TABLE table2
ADD CONSTRAINT uk_s_tellphone
UNIQUE(s_tellphone);

Schemas

CHECK约束

分为表约束和列约束,用于限制字段值在某个范围。

Introduction

添加CHECK约束
ALTER TABLE table2
ADD sex CHAR(2);--在table表中添加sex,数据类型为CHAR,用来存放性别
GO
ALTER TABLE table2
ADD CONSTRAINT ck_sex CHECK(sex in('男','女'));

注:此时sex列数据类型不能是bit,如果填写bit,只能存储0和1,用CHECK约束限制结果为男和女就会报错。

  ->The DBA(数据库管理员) must begin by creating the tables
and constraints(约束) and loading the data. Impose(强加上) integrity constraints on the
columns 。

在创建表的时候添加CHECK 约束
CREATE TABLE table3(
    t3_id VARCHAR(12),
    t3_name VARCHAR(20),
    t3_class VARCHAR(12),
    t3_type VARCHAR(12),
    CONSTRAINT ck_t3_type CHECK(t3_type in('类型1','类型2','类型3')) 
)

添加了约束后如果插入不符合约束的数据

INSERT INTO table3(
    t3_id,
    t3_name,
    t3_class,
    t3_type
)VALUES(
'2018038219',
'李建',
'社会与科学',
'任何数据'
);

消息547,级别16,状态0,第1 行
INSERT 语句与CHECK
约束”ck_t3_type”冲突。该冲突发生于数据库”test”,表”dbo.table3″, column
‘t3_type’。

语句已终止。

  ->Then provide views(视图) of the data, virtually
restructuring the physical tables into variant(不同的) table forms, to
simplify access to data.

删除CHECK约束
ALTER TABLE table3
DROP CONSTRAINT ck_t3_type;

  ->Provide security(安全性),
so that only authorized users are able to read or update certain
confidential(机密的) data.

DEFAULT约束

通过定义列的默认值或使用数据库的默认值对象绑定表列,来指定列的默认值。

  ->The structure of the various tables, views, and other objects
of a database are made available to the DBA through a set of
system-defined tables, called system
catalogs(系统目录).

在建表时添加DEFAULT约束
CREATE TABLE table3(
    t3_id VARCHAR(12),
    t3_name VARCHAR(20),
    t3_class VARCHAR(12),
    t3_type VARCHAR(12) DEFAULT '类型1' 
)

 

删除DEFAULT约束
ALTER TABLE table3
DROP CONSTRAINT DF__table3__t3_type__3D5E1FD2;
--DF__table3__t3_type__3D5E1FD2是DEFAULT约束的约束名

Integrity(完整性)

添加约束不指定约束名
ALTER TABLE table3
ADD DEFAULT '类型2' FOR t3_type;
GO

  ->保证数据完整性(主要指数据的正确性与一致性)是数据库管理员的最重要任务之一。

添加约束指定约束名
ALTER TABLE table3
ADD CONSTRAINT df_t3_type
DEFAULT '类型2' FOR t3_type;
GO

  ->可以通过约束(Constraint)、规则(Rule)或者缺省值保证数据的完整性,也可以在应用程序层保证数据完整性(这是应用程序设计的问题),或通过触发器保证。

NOT NULL约束

约束字段值不为空。

  ->数据完整性类型包括:实体完整性、参照完整性和用户定义完整性  

建表时设置NOT NULL约束
CREATE TABLE table3(
    t3_id VARCHAR(12) NOT NULL,
    t3_name VARCHAR(20) NOT NULL,
    t3_class VARCHAR(12) NOT NULL,
    t3_type VARCHAR(12) NOT NULL 
)

    ->实体完整性(Entity Integrity)
:现实世界的实体是可区分的,即它们具有某种唯一性标识。相应地,关系模型中主键应作为唯一性标识。因此实体完整性规则规定基本关系的所有主键(Primary
Key)都不能取空值(NULL) 。

为已存在的列添加NOT NULL约束
ALTER TABLE table3
ALTER COLUMN t3_type VARCHAR(12) NOT NULL;

   
 ->参照完整性(Referential Integrity)
:参照完整性维护表与表之间的相关性,通常用“主键(Primary
Key)/外键(Foreign Key)”保证,其中Foreign
Key可以取NULL值,或取其参照表中Primary Key或者候选键的取值。

删除NOT NULL约束
ALTER TABLE table3
ALTER COLUMN t3_type VARCHAR(12) NULL;
GO

    ->用户定义的完整性(User_defined
Integrity
):针对某一具体数据的约束条件,由应用环境决定。例如:某个属性具有缺省值、某个属性必须取惟一值(UNIQUE)、某个非主属性不能取NULL值、某个属性的取值范围在0~100之间(CHECK)等等。

自定义默认值对象维护数据完整性

CREATE DEFAULT date_today AS GETDATE();
--新建默认值对象名date_today,默认值为getdate()函数,获取当前日期
GO
EXEC sp_addtype date_time,'date','NULL';
--利用存储过程新建自定义数据类型date_time,参照系统数据类型date
GO
EXEC sp_bindefault 'date_today','date_time';
--将默认值对象date_today绑定到自定义数据类型date_time上
GO
CREATE TABLE table3(--新建table3,设置字段t3_date的数据类型为date_time
    t3_id VARCHAR(12),
    t3_name VARCHAR(20),
    t3_class VARCHAR(12),
    t3_type VARCHAR(12),
    t3_date date_time
);
GO
INSERT INTO table3(--为新建表table3插入一条数据,不指定t3_date的值,看默认值是否有效
    t3_id,
    t3_name,
    t3_class,
    t3_type
)VALUES(
'2018038220',
'李建',
'社会与科学',
'类型1'
);
GO
SELECT * FROM table3;--查询table3数据,看t3_date是否有默认值为当前日期

查询结果如下
图片 2

ALTER TABLE table3
ADD t3_date1 DATE;--在table3表中新增一列t3_date1,数据类型为DATE
GO
EXEC sp_bindefault 'date_today','table3.t3_date1';
--直接将默认值对象date_today绑定到table3的t3_date1列
GO
INSERT INTO table3(--为新建表table3插入一条数据,不指定t3_date和t3_date1的值,看默认值是否有效
    t3_id,
    t3_name,
    t3_class,
    t3_type
)VALUES(
'2018038221',
'李建',
'社会与科学',
'类型'
);
GO
SELECT * FROM table3;
GO

查询结果如下
图片 3

  ->数据完整性具体实现包括两类

存储过程查询默认值对象的所有者
USE test
EXEC sp_help date_today;
GO

结果如图所示
图片 4

    ->声明性数据完整性
:声明性数据完整性用约束(Constraint)、规则(Rule)
在数据库中提供保证,这是保证完整性的较好方法。它驻留在数据库内,编程简单,开销小,能更集中管理和保证数据的一致性

删除默认值对象
DROP DEFAULT date_today;

删除不成功,提示以下信息:
消息3716,级别16,状态3,第1 行
无法删除默认值’date_today’,因为它已绑定到一个或多个列。
那么我们知道,当一个默认值对象绑定了列之后,就无法删除它,如果想要删除,就必须先解绑。在上面的操作中,我们的默认值对象date_today绑定了test数据库table3表的t3_date1字段。

    ->过程性数据完整性
:过程性数据完整性用触发器和应用程序代码保证,通常较为复杂、开销较大,但可以执行更多的业务规则。
通常,过程性数据完整性是声明性数据完整性的补充

对列解绑默认值对象
USE test
GO
EXEC sp_unbindefault 'table3.t3_date1';

此时我们再次尝试删除默认值对象,发现还是不行,此时要注意,在上面的操作中,默认值对象date_today不止绑定了t3_date1列,还绑定了自定义数据类型date_time,并将该数据类型定义给了t3_date列,我们还需要对date_time解绑默认值对象。

EXEC sp_unbindefault 'date_time';

此时再次删除默认值对象,就可以成功删除。删除默认值对象后,原先绑定的字段不会再有默认值。

           图片 5

自定义规则维护数据完整性

规则是对列或自定义数据类型的值的规定和限制。自定义规则的表达式一定要返回布尔类型的值,并且表达式中不能包含多个变量。

CREATE RULE score_rule AS @math_score>=0;
GO--新建规则score_rule,参数@math_score
EXEC sp_addtype 'score_type','float','NULL';
GO--新建自定义数据类型score_type
CREATE TABLE table_score(--新建表table_score,预设mt_score和at_score字段用于绑定规则
s_id VARCHAR(4),
s_name VARCHAR(10),
mt_score float,--该字段将用于规则score_rule绑定到列
at_score score_type--该字段将用于规则score_rule绑定到自定义数据类型
);
GO
EXEC sp_bindrule 'score_rule','score_type';
GO--将score_rule规则绑定到自定义数据类型score_type
EXEC sp_bindrule 'score_rule','table_score.mt_score';
GO--将score_rule规则绑定到table_score表的mt_score列

----以下进行规则测试
INSERT INTO table_score(
s_id,
s_name,
mt_score,
at_score
)VALUES(
'0001',
'张华',
'-1',
'-1'
);
GO

进行违反规则的插入后,数据库报错
消息513,级别16,状态0,第1 行
列的插入或更新与先前的CREATE RULE
语句所指定的规则发生冲突。该语句已终止。冲突发生于数据库’test’,表’dbo.table_score’,列’mt_score’。

语句已终止。
很明显,mt_score的插入值为-1,违反了必须大于等于0的规则,数据库报错。将mt_score的插入值改成符合规则的数据,再次运行插入语句,数据库依然会报错,因为at_score字段的插入值也是违反规则的。将两个数据改成符合规则的返回,执行成功。

注:新建规则时表达式一定要是返回布尔类型的值,否则会报错

消息4145,级别15,状态1,过程sum_score,第1 行
在应使用条件的上下文(在’;’ 附近)中指定了非布尔类型的表达式。

                 图片 6

删除自定义规则

和自定义默认值对象一样,删除自定义规则要求该规则先与字段和自定义数据类型解绑。在上面的操作中,score_rule规则与自定义数据类型score_type以及列mt_score已绑定。因此执行以下语句:

EXEC sp_unbindrule 'score_type';
GO--解除规则score_rule与score_type之间的绑定
EXEC sp_unbindrule 'table_score.mt_score';
GO--解除规则score_rule与表table_score的mt_score列的绑定
DROP RULE score_rule;--删除score_rule规则

注:经过试验,一个列只能绑定1条规则,如果对一个列绑定2条规则,前一条规则会被后一条规则顶替。

           

查看自定义规则
EXEC sp_help 'score_rule';

结果如图所示
图片 7

-》》》完整性再阐述

查看自定义规则的定义信息
EXEC sp_helptext 'score_rule';
GO

结果如图所示
图片 8

     数据有着复杂的数据类型,用来满足不同的需求。实际应用中的数据也有着一定的取值范围,如年龄不小于0,性别只有男女等。而表之间的联系使相连的字段要保持一致和完整。但是,实际操作无法保证插入和删除的数据都符合要求,不符合要求的操作极可能会破坏数据的完整性,对数据库的可靠性和运行能力造成威胁。

因此存放数据集的数据库必须要对数据表和列有所限制和规范,为此SQLServer系统使用一系列的方法来维护数据完整性。

数据的完整性:数据的完整性是指数据库中数据的正确性和一致性,表内的数据不相矛盾,表间的数据不相矛盾,关联性不被破坏。

为此有了以下实施完整性的途径:

1.        对列数据的控制:数据验证约束、默认值约束、规则。

2.        对列的控制:主键约束、唯一性约束、标识列。

3.        对表之间、列之间关系的控制:外键约束、数据验证约束、触发器、存储过程。

这些途径可以按照类型分为:约束、规则、默认值、触发器、存储过程。

约束分为以下几点:

l        主键约束

主键:PRIMARY
KEY,主关键字,用来限制列的数据具有唯一性且不为空,即这一字段的数据没有重复的数据值且不能有空值。每个表只能有一个主键,一般用来做标识。

l        外键约束

外键:FOREIGN
KEY,外键用来在两个表的数据之间建立连接,它可以是一列或者多列。一个表可以有一个或者多个外键。外键对应的是参照完整性,一个表的外键可以为空值,若不为空值则每一个外键值必须等于另一个表中主键的某个值。

l        标识列

可以自动编号的列称为标识列或IDENTITY约束。IDENTITY约束就是为那些数值顺序递增的列准备的约束,自动完成数值的添加。每个表只能有一个标识列,标志数据不能由用户输入,用户只需要填写标志种子(标识列的第一个数据)和标志增量(依次增加的数),系统自动生成数据并填入表。标志种子和标志增量都是非零整数,位数小于等于10。默认两者均为1。

l        唯一性约束

又叫UNIQUE约束,在主键约束中也用到了唯一性,不同的是一个表中可以有多个这样的唯一性列,却只能有一个主键。这里的唯一性列可以为空但是只能有一行数据为空。适用于不是主键但却仍旧要求具有唯一性的字段。

l        非空约束

一个列是否允许有空值,就是这里的空和非空约束,即NULL与NOT
NULL约束。NULL就是允许为空,NOT
NULL就是不允许为空。NULL不同于0和“”,0和“”都表示为该行有数据,而NULL是没有数据。

l        数据验证约束

又称做CHECK约束,它通过给定条件(逻辑表达式)来检查输入数据是否符合要求,以此来维护数据完整性。

l        默认值约束

又称作DEFAULT约束。将常用的数据值性以为默认值可以节省用户输入的时间,在非空字段中定义默认值可以减少错误发生。在实际应用中,默认值还可以是结果能变的函数。

 

规则:

规则是独立的SQL
Server对象,跟表和视图一样是数据库的组成部分。规则的作用和CHECK约束类似,用于完成对数据值的检验。它可以关联到多个表,在数据库中有邮局插入、修改时,验证新数据是否符合规则,是实现域完整性的方式之一。规则在定义时并没有定义它的检测对象,而是在创建后绑定到对象来检测数据。

创建规则

CREATE RULE 规则名

AS

条件表达式

在这里的条件表达式使用逻辑表达式,表达式中要有一个@开头的变量,代表用户的输入数据,可以看做是代替WHERE后面的列名。

规则在绑定之后才可以使用,规则的绑定需要时用系统存储过程sp_bindrule.

USE 数据库名

Go

Sp_bindrule 规则名 表名.字段名

使用存储过程sp_help来查看规则。

规则的特点(与CHECK的不同)

1)       规则是SQL Server的对象而CHECK是一种约束,是表定义的一部分。

2)       CHECK的优先级要高于规则。

3)       一个列只能使用一个规则却可以使用多个CHECK约束。

4)       规则可以应用于多个列,CHECK约束只针对它定义的列。

5)       规则创建一次可以使用多次而CHECK约束需要多次创建。

默认值:

CREATE DEFAULT 默认值名

AS 常亮表达式

与创建规则一样,默认值的定义不能包含列名,需要绑定到列或是其他数据库对象才能使用。一个列只能绑定一个默认值

 

Definition 7.1.2 Column Constraint

       NOT NULL -If NOT NULL appears in a col_constr, then the DEFAULT
clause cannot specify NULL;

       CONSTRAINT-allows us to specify a name for each constraint other
than NOT NULL, so that we can later drop the constraint with an ALTER
Table statement.

       UNIQUE- It can be specified even if NOT NULL is not, and the
column is then constrained so that all non-null values in the table are
unique, but multiple nulls can exist for this column.       

       PRIMARY KEY -A column with the PRIMARY KEY column constraint is
implicitly defined to be NOT NULL and UNIQUE. The UNIQUE clause and
PRIMARY KEY clause cannot both be used for a column, although the
PRIMARY KEY clause and the NOT NULL clause can be used together.

       CHECK

       EXAMPLE

       图片 9

              图片 10

 

                      图片 11

                    触发器

                 

触发器的介绍

 

  1. 1

    触发器(trigger)是个特殊的存储过程,它的执行不是由程序调用,也不是手工启动,而是由事件来触发,当对一个表进行操作(
    insert,delete,
    update)时就会激活它执行,触发器经常用于加强数据的完整性约束和业务规则等。其实往简单了说,就是触发器就是一个开关,负责灯的亮与灭,你动了,它就亮了,就这个意思。

    图片 12

    END

触发器的分类

 

  1. 1

    DML( 数据操纵语言 Data Manipulation
    Language)触发器:是指触发器在数据库中发生DML事件时将启用。DML事件即指在表或视图中修改数据的insert、update、delete语句。

  2. 2

    DDL(数据定义语言 Data Definition
    Language)触发器:是指当服务器或数据库中发生(DDL事件时将启用。DDL事件即指在表或索引中的create、alter、drop语句也。

  3. 3

    登陆触发器:是指当用户登录SQL SERVER实例建立会话时触发。

    END

DML触发器介绍

 

  1. 1

    在SQL SERVER
    2008中,DML触发器的实现使用两个逻辑表DELETED和INSERTED。这两个表是建立在数据库服务器的内存中,我们只有只读的权限。DELETED和INSERED表的结构和触发器所在的数据表的结构是一样的。当触发器执行完成后,它们也就会被自动删除:INSERED表用于存放你在操件insert、update、delete语句后,更新的记录。比如你插入一条数据,那么就会把这条记录插入到INSERTED表:DELETED表用于存放你在操作
    insert、update、delete语句前,你创建触发器表中数据。

  2. 2

    触发器可通过数据库中的相关表实现级联更改,可以强制比用CHECK约束定义的约束更为复杂的约束。与
    CHECK
    约束不同,触发器可以引用其它表中的列,例如触发器可以使用另一个表中的
    SELECT
    比较插入或更新的数据,以及执行其它操作。触发器也可以根据数据修改前后的表状态,再行采取对策。一个表中的多个同类触发器(INSERT、UPDATE
    或 DELETE)允许采取多个不同的对策以响应同一个修改语句。

  3. 3

    与此同时,虽然触发器功能强大,轻松可靠地实现许多复杂的功能,为什么又要慎用?过多触发器会造成数据库及应用程序的维护困难,同时对触发器过分的依赖,势必影响数据库的结构,同时增加了维护的复杂程序。

    END

触发器步骤详解

 

  1. 1

    首先,我们来尝试创建一个触发器,要求就是在AddTable这个表上创建一个Update触发器,语句为:

    create trigger mytrigger on AddTable
    for update

    图片 13

  2. 2

    然后就是sql语句的部分了,主要是如果发生update以后,要求触发器触发一个什么操作。这里的意思就是如果出现update了,触发器就会触发输出:the
    table was updated!—By 小猪也无奈。

    图片 14

  3. 3

    接下来我们来将AddTable表中的数据执行一个更改的操作:

    图片 15

  4. 4

    执行后,我们会发现,触发器被触发,输出了我们设置好的文本:

    图片 16

  5. 5

    那触发器创建以后呢,它就正式开始工作了,这时候我们需要更改触发器的话,只需要将开始的create创建变为alter,然后修改逻辑即可:

    图片 17

  6. 6

    如果我们想查看某一个触发器的内容,直接运行:exec sp_helptext
    [触发器名]

    图片 18

  7. 7

    如果我想查询当前数据库中有多少触发器,以方便我进行数据库维护,只需要运行:
    select * from sysobjects where xtype=’TR’

    图片 19

  8. 8

    我们如果需要关闭或者开启触发器的话,只需要运行:

    disable trigger [触发器名] on database –禁用触发器

    enable trigger [触发器名] on database –开启触发器

    图片 20

  9. 9

    那触发器的功能虽大,但是一旦触发,恢复起来就比较麻烦了,那我们就需要对数据进行保护,这里就需要用到rollback数据回滚~

    图片 21

  10. 10

    第九步的意思就是查询AddTable表,如果里面存在TableName=newTable的,数据就回滚,触发器中止,那我们再进行一下测试,对AddTable表进行更改,发现,触发update触发器之后,因为有数据保护,触发器中止:图片 22

    图片 23

  11.  

 图片 24

图片 25

图片 26

图片 27

图片 28

图片 29

图片 30

图片 31

图片 32

图片 33

图片 34

图片 35

图片 36

 

 图片 37

图片 38

图片 39

图片 40

图片 41

图片 42

图片 43

图片 44

图片 45

图片 46

图片 47

图片 48

图片 49

图片 50

图片 51

图片 52

图片 53

图片 54

图片 55

图片 56

图片 57

图片 58

图片 59

图片 60

图片 61

图片 62

图片 63

图片 64

 

相关文章