2008从入门到精通

CHECK约束

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

必赢官网,l        唯一性约束

约束

                      必赢官网 1

添加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;

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

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

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

EXEC sp_unbindefault 'date_time';

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

Definition 7.1.2 Column Constraint

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

Schemas

主关键字约束(Primary Key Constraint)

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

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

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

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

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

UNIQUE约束

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

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

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

删除默认值对象
DROP DEFAULT date_today;

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

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

添加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约束限制结果为男和女就会报错。

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

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

结果如图所示
必赢官网 4

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

DEFAULT约束

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

l        标识列

在创建表的时候添加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’。

语句已终止。

触发器的介绍

 

  1. 1

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

    必赢官网 5

    END

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

结果如图所示
必赢官网 6

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

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

规则:

删除自定义规则

和自定义默认值对象一样,删除自定义规则要求该规则先与字段和自定义数据类型解绑。在上面的操作中,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条规则,前一条规则会被后一条规则顶替。

AS

建表时设置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 
)

Integrity(完整性)

NOT NULL约束

约束字段值不为空。

l        数据验证约束

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

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

删除Primary Key
ALTER TABLE table1
DROP CONSTRAINT t_idss;

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

           

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

Catalogs

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

结果如图所示
必赢官网 7

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

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

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

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

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

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 行
在应使用条件的上下文(在’;’ 附近)中指定了非布尔类型的表达式。

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

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。

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

外关键字约束(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走,不能反着来。

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

integrity constraints (完整性约束)

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

Sp_bindrule 规则名 表名.字段名

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

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

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

                 必赢官网 8

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

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

触发器步骤详解

 

  1. 1

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

    create trigger mytrigger on AddTable
    for update

    必赢官网 9

  2. 2

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

    必赢官网 10

  3. 3

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

    必赢官网 11

  4. 4

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

    必赢官网 12

  5. 5

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

    必赢官网 13

  6. 6

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

    必赢官网 14

  7. 7

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

    必赢官网 15

  8. 8

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

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

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

    必赢官网 16

  9. 9

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

    必赢官网 17

  10. 10

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

    必赢官网 19

  11.  

 必赢官网 20

必赢官网 21

必赢官网 22

必赢官网 23

必赢官网 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

 

       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.

约束分为以下几点:

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

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

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

l        外键约束

              必赢官网 61

l        非空约束

CREATE VIEW

CREATE RULE 规则名

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

           必赢官网 62

CREATE DEFAULT 默认值名

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

from Database Design to Physical Form

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

发表评论

电子邮件地址不会被公开。 必填项已用*标注