①数据库的完整性是指数据的正确性和相容性金沙js娱乐场官方网站:,id列创建主键

id列创建主键,在创建表时创建Primary Key,The DBA(数据库管理员) must begin by creating the tables,The structure of the various tables,1.数据库的完整性,一、数据库完整性概述,对于所有行,比如某一行出现的值就不允许出现在其他行

金沙js娱乐场官方网站 17
建表时设置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 
)

                    触发器

  
检查数据是否满足完整性约束条件的机制称为完整性检查。一般在INSERT、UPDATE、DELETE语句执行后开始检查。

3、唯一约束

  唯一约束与主键比较相似,共同点在于它们都要求表中指定的列(或者列的组合)上有一个唯一值,区别是唯一约束没有被看作表中记录的唯一标识符(即使你可以按这样的方式使用也有效),而且可以有多个唯一约束(而在每个表中只能有一个主键)。

  一旦建立了唯一约束,那么指定列中的每个值必须是唯一的。如果更新或者插入一条记录在带唯一约束的列上有已经存在的值的记录,SQLServer将抛出错误,拒绝这个记录。

  和主键不同,唯一约束不会自动防止设置一个NULL值,是否允许为NULL由表中相应列的NULL选项的设置决定,但即使确实允许NULL值,一张表中也只能够插入一个NULL值(如果允许多个,那就不叫唯一了)。

  在已存在的表上创建唯一约束:

alter table Account
    add constraint AK_AccountName    --约束名
    unique (Account_Name)    -- 列名

  AK代表替换键(Alternate Key),唯一约束也叫替换键。

  主键和唯一约束的区别:

  •    
    主键约束不允许出现NULL值。任何索引的索引键都不允许包含null值。但唯一约束允许包含NULL值,但唯一约束把两个NULL值当作重复值,所以施加了唯一约束的每一列只允许包含一个NULL值。
  •    
    创建主键时会自动创建聚集索引,除非当前表中已经含有了聚集索引或是创建主键时指定了NONCLUSTERED关键字。
  •    
    创建唯一约束时会自动创建非聚集索引,除非你指定了CLUSTERED关键字并且当前表中还没有聚集索引。
  •     每个表中只能有一个主键,但可以由多个唯一约束。
查看自定义规则的定义信息
EXEC sp_helptext 'score_rule';
GO

结果如图所示
金沙js娱乐场官方网站 1

CREATE VIEW

(3)要使用TRansact-SQL修改PRIMARY
KEY,必须先删除现有的PRIMARY KEY约束,然后再重新创建。

8、触发器

  触发器也能够用于实现数据完整性,这个内容比较多,新建一篇文章叙述。

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

Introduction

规则用以限制存储在表中或用户自定义数据类型的值,是独立的数据库对象。

9、如何选择

  经过以上的学习,对于数据完整性,你会发现有很多种可以选择,那么如何挑选合适的约束呢?

限制 优点 缺点
约束

快速

可以引用其他列

在命令执行前发生

遵循ANSI标准

必须对每个表重新定义

不能引用其他表

不能绑定到数据类型

规则

独立的对象

可重用

可以绑定到数据类型

命令执行前发生

稍慢

不能跨列使用

不能引用其他表

实际上只用于向后兼容

默认值

非常灵活

可以引用其他列或其他表

可以通过.NET引用SQL Server之外的其他信息

在命令执行之后发生

系统开销很大

  如果要实现更健壮的逻辑模型以及广泛使用用户自定义数据类型,则一般使用规则和默认值。在这种情况下规则和默认值可以提供很多功能,容易管理,而不用太多的编程开销。

  只有在不能选择约束时使用触发器。和约束一样,他们被附加到表中,而且必须对创建的每个表重新定义。好的方面是触发器几乎可以做数据完整性方面的任何操作。实际上再没有出现外键时,他们常被用作外键的替代品。

  而在其他情况下,应将约束作为数据完整性解决方案的选择。它们执行速度快,而且不难创建。他们的缺点是功能有限(除了外键约束,都不能引用其他表),而且对于通用约束逻辑来说,需要一次次地重新定义。

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

结果如图所示
金沙js娱乐场官方网站 2

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

①创建默认对象

2、外键约束

  外键既能确保数据完整性,也能表现表之间的关系。添加了外键之后,插入引用表的记录要么必须被引用表中被引用列的某条记录匹配,要么外键列的值必须设置为NULL。

  外键和主键不一样,每个表中的外键数目不限制唯一性。在每个表中,每一有-~253个外键。唯一的限制是一个列只能引用一个外键。一个列可以被多个外键引用。

  1、创建表的时候创建外键

金沙js娱乐场官方网站 3😉

create table orders
(
    orderId        int identity    not null
        primary key,
    customerId    int                not null
        foreign key references customer(customerId)  --约束类型-外键-引用表(列名)
);

金沙js娱乐场官方网站 4😉

  2、在已存在的表中添加一个外键

  假设上面的代码去掉了添加外键行,那么可以书写代码如下:

alter table orders
    add constraint FK_Orders_CustomerId        --添加约束 名称
        foreign key (customerId)    references customer(customerId)    --外键约束,外键列名,被引用列名

  刚添加的约束和之前添加的约束一样生效,如果某行引用customerId不存在,那么就不允许把该行添加到Orders表中。

  3、级联动作

  外键和其他类型键的一个重要区别是:外键是双向的,即不仅是限制子表的值必须存在于父表中,还在每次对父表操作后检查子行(这样避免了孤行)。SQLServer的默认行为是在子行存在时“限制”父行被删除。然而,有时会自动删除任何依赖的记录,而不是防止删除被引用的记录。同样在更新记录时,可能希望依赖的记录自动引用刚刚更新的记录。比较少见的情况是,你可能希望将引用行改变为某个已知的状态。为此,可以选择将依赖行的值设置为NULL或者那个列的默认值。

  这种进行自动删除和自动更新的过程称为级联。这种过程,特别是删除过程,可以经过几层的以来关系(一条记录依赖于另一条记录,而这另一条记录又依赖其他记录)。在SQLServer中实现级联动作需要做的就是修改外键语法-只需要在添加前面加上ON子句。例如:

alter table orders
    add constraint FK_Orders_CustomerId        --添加约束 名称
        foreign key (customerId)    references customer(customerId)    --外键约束,外键列名,被引用列名
        on update     no action    --默认  修改时不级联更新子表
        on delete     cascade      --删除时级联删除依赖行

  当在进行级联删除时,如果一个表级联了另一个表,而另一个表又级联了其他表,这种级联会一直下去,不受限制,这其实是级联的一个危险之处,很容易一个不小心删掉大量数据。

  级联动作除了no action,cascade之外,还有set null和set
default。后两个是在SQLServer2005中引入的,如果要兼容到SQLServer2000的话,要避免使用这两个级联动作。但是他们的才做是非常简单的:如果执行更新而改变了一个父行的值,那么子行的值将被设置为NULL,或者设置为该列的默认值(不管SET
NULL还是SET DEFAULT)。

  4、外键其他方面的考虑

    外键中的之只有相中可能的选择:

    1、在列中填充与被引用表中的相应列相匹配的值。

      通过定义引用列为NOT
NULL,可以使外键完全是必须的(即用户添加数据时必须引用表中必须有相匹配的一行数据)。

    2、不填充任何值,而使该值为NULL。

      允许引用列有NULL值时,用户可以选择不提供值-即使在被引用表没有与NULL值匹配的行,还是允许插入。

主关键字约束(Primary Key Constraint)

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

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

可以使用CREATE
DEFAULT语句创建默认对象。其语法格式如下:

7、规则和默认值

  规则和默认值的应用要早于CHECK和DEFAULT约束。他们是较老的SQL
Server备用约束的一部分,当然也不是没有优点。自7.0版本之后,MicroSoft列出规则和默认值只是为了向后兼容,而不准备在以后继续支持这个特性。因此对于生成新代码时,应该使用约束。

  规则、默认值与约束的本质区别是:约束是一个表的特征,本身没有存在形式,而规则和默认值是表和自身的实际对象,本身存在。约束是在表定义中定义的,而规则和默认值是单独定义,然后”绑定到”表上。

  规则和默认值的独立对象特性使得它们可以在重用时不用重新定义。实际上,规则和默认值不限于被绑定到表上,它们也可以绑定到数据类型上。

  7.1规则

   规则和CHECK约束非常相似。它们之间的唯一区别是规则每次只能作用于一个列。可以将同一规则分别绑定到一个表中的多个列,但是规则分别作用于每个列,根本不会意识到其他列的存在。像QtyShipped

<=
QtyOrdered这样的约束不适用于规则(它引用多个列),而LIKE([0-9][0-9][0-9])这样的定义适用于规则。

  定义规则:

  下面定义一个规则,这样就可以首先看到区别所在:

CREATE RULE Age18Rule
    AS @Age > 18  

  这里比较的是一个变量,不管被检查的列是什么值,这个值将用于替换@Age。因此在这个示例中,规则所绑定的任何列的值都必须大于18。

  到目前为止,只是创建了一个规则,但这个规则还没对任何表的任何列起作用,要激活这个规则需要使用一个存储过程:sp_bindrule。

  将规则Age18绑定到表person的person_age列:

  EXEC sp_bindrule 'Age18Rule','person.person_age';

  此时,如果我们执行不满足规则的插入操作:

insert into person values ('绑定规则',17)

   将返回如下报错信息:

消息 513,级别 16,状态 0,第 1 行
列的插入或更新与先前的 CREATE RULE 语句所指定的规则发生冲突。该语句已终止。冲突发生于数据库 'Nx',表 'dbo.person',列 'person_age'。
语句已终止。

  很明显,规则已经生效。

  要特别注意的是,在绑定之前,规则与任何表,任何列都没有关系,因此在绑定的时候,第二个参数要加.指定表名与列名(tablename.column)。

  解除绑定规则:

  当我们需要在一个列上解除绑定规则的时候,只要执行sp_unbindrule

  删除刚才绑定的规则:

EXEC sp_unbindrule 'person.person_age';

  这时候,执行刚才的插入操作,就不会报错了。

  删除规则:

  如果希望将规则从数据库中彻底删除,那么可以在表中使用非常熟悉的DROP语法。

DROP RULE <rule name>

  如删除刚才创建的那条规则:

DROP RULE Age18Rule

  7.2默认值

  默认值类似于DEFAULT。实际上默认值-DEFAULT约束的关系与规则-CHECK约束的关系差不多。区别在于它们被追加到表中的方式和对用户自定义数据类型的默认值(是对象,而不是约束)支持。

  定义默认值的语法和定义规则类似:

  CREATE DEFAULT <default_name>
  AS <default value>

  创建默认值:

  因此,假设要为Age定义一个值为0的默认值:

CREATE DEFAULT AgeDefault
AS 0

  绑定默认值:

  同样,如果不绑定到一个对象上,则默认值是不起作用的。要绑定的话,使用存储过程sp_bindefault。

  EXEC sp_bindefault 'AgeDefault','person.person_age';

  要从表中解决默认值的绑定,使用sp_unbindefault:

  sp_unbindefault 'person.person_age';

  删除默认值:

  如果要从数据库中彻底删除一个默认值,则可以使用DROP语法,与删除规则相同:

  DROP DEFAULT AgeDefault

  7.3确定哪个表和数据类型使用给定的规则或默认值

  如果希望删除或者修改规则或默认值。那么您可以先看看哪些表和数据类型在使用它们。SQL
Server还是采用系统存储过程解决这个问题。这个存储过程是sp_depends。其语法如下所示:

  EXEC sp_depends <object name>

  sp_depends提供了依赖于你所查询对象的所有对象列表。

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

 

①PRIMARY
KEY 约束

二、约束命名

  在学习约束之前,首先来了解下为约束命名需要注意哪些地方。

  SQLServer在我们不提供名称时,会自动创建名称,但是由系统自动创建的名称并不是特别有用。

  例如,系统生成的主键名称可能是这样的:PK_Employees_145C0A3F。 

  PK代表主键(primary
key),Employees代表在Employees表中,而剩下的“145C0A3F”部分是为了保证唯一性而随机生成的值。只有通过脚本创建才会得到这种值,如果是通过Managerment
Studio创建表,那么就直接是PK_Employees。

  对于系统自动生成的Check约束名称如:CK_Customers_22AA2996。CK代表这是一个Check约束,Customers代表是在Customers表中,后面的22AA2996还是一个随机数。如果一个表中有多个Check约束,则命名可能如下:

  CK_Customers_22AA2996

  CK_Customers_25869641

  CK_Customers_267ABA7A

  如果你需要修改这些约束其中的一个,那么你很难分辨这些约束到底是哪一个。

  因此,为了能够一眼看上去就知道这个约束是用来干什么的,我们应该使用一种简单明了的短语来进行命名。

  例如要确保某一列电话号码格式正确的约束,我们可以使用命名CK_Customers_PhoneNo这样的短语来命名。

  总之命名要做到以下几点:

  1、一致性

  2、通俗易懂

  3、满足以上两个条件的情况下简化名称。

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

       CHECK

(4)创建表时指定主键,sql
server会自动创建一个名为“PK_”且后跟表名的主键索引。如果不指定索引类型,则默认为聚集索引。该索引只能在删除与它保持联系的表或主键约束时才能删除。

三、键约束

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。

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

一、数据库完整性概述

1、主键约束

   主键是每行的唯一标识符,仅仅通过它就能准确定位到一行,其中主键列在整个表中不能有重复,必须包含唯一的值(不能为NULL)。由于主键在关系数据库中的重要性,因此它是所有键和约束中最重要的。

   下面来说说主键的创建方式

  1、在创建表的时候创建主键约束。

create table customer
(
    customerId        int identity    not null
        primary key,  --创建主键约束
    CustomerName    nvarchar(30)    not null
);

  怎么样,非常简单吧!

  2、在已存在的表上创建主键约束

  现在假设已经存在了一张表,但是还没有主键约束:

alter table person
    add constraint PK_Employee_Id  --外键名称
    primary key(personId)  --personId 字段名

  alter名称告诉SQLServer如下信息:

  1、添加了一些内容到表中(也可以删除表中的某些内容)

  2、添加了什么内容(一个约束)

  3、对约束的命名(允许以后直接访问约束)

  4、约束的类型(主键约束)

  5、约束应用于哪个列。

  3、复合主键的创建

  如果实在Management
Studio中,创建复合主键,只需要按住Ctrl键,选中两个列,然后设置为主键就OK了,非常简单。下面主要讲述使用T-SQL创建复合主键的方法:

ALTER TABLE 表名 WITH NOCHECK ADD 
CONSTRAINT [PK_表名] PRIMARY KEY NONCLUSTERED 
( 
[字段名1], 
[字段名2] 
) 

  在多对多联系中,常常会有一张表来描述其他两张表的关系,就以此读者和书为例子:

金沙js娱乐场官方网站 5😉

ALTER TABLE ReaderAndBook 
ADD 
CONSTRAINT [PK_ReaderAndBook] PRIMARY KEY NONCLUSTERED 
( 
ReaderId, 
BookId 
) 

金沙js娱乐场官方网站 6😉

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

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

(6)UNIQUE约束与主键约束的区别: 主键也强制执行唯一性,但主键不允许空值,而且每个表中主键只能有一个,但UNIQUE列可以有多个,可以取空值。

一、约束的分类

  在SQLServer中,有3种不同类型的约束。

  1、实体约束

    实体约束是关于行的,比如某一行出现的值就不允许出现在其他行,例如主键。

  2、域约束

    域约束是关于列的,对于所有行,某一列有那些约束,例如CHECK约束。

  3、参照完整性约束

    如果某列的值必须与其他列的值匹配,那就意味着需要一个参照完整性约束,例如外键。

CHECK约束

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

 

sp_bindefault
[@defname = ] ‘default’, [@objname
= ] ‘object_name’   [,
[@futureonly = ] ‘futureonly_flag’]

6、禁用约束

   有时我们想暂时或永久地消除约束。但是SQL
Server并没有提供删除约束的方法。SQL
Server只允许禁用外键约束或CHECK约束,而同时保持约束的完整性。

   禁用一个数据完整性规则通常是因为已经有无效数据了。这样的数据通常分为以下两类:

  1、在创建约束时已经在数据库中的数据

  2、在约束创建以后希望添加的数据

  SQL
Server允许禁用完整性检查一段时间来对例外的无效数据作处理,然后再重新启用完整性(不是物理删除数据完整性约束)。

    注意:不能禁用主键约束或者唯一约束

  6.1、在创建约束时,忽略检查之前的不满足数据

  要添加一个约束,但是有不应用到已存在的数据中,可以再执行Alter
Table语句添加约束时使用WITH NOCHECK选项。

  按照上面创建Check约束的方法,已经Alter
Table时,表中本身已经存在不符合的数据,那么Alter Table操作将被SQL
Server拒绝执行。除非已经存在的所有数据都满足CHECK约束的条件,否则SQL
Server不会执行创建约束的命令。要解决这个问题,我们可以添加WITH NOCHECK。

  我们先新建一个表只有3个字段的表,Id、姓名、年龄,并在里面插入一条不满足要求的数据:

insert into Account values (23,'洪',17)

   然后执行添加约束命令:

alter table Account
    add constraint CN_AccountAge18
    check 
    (Account_Age > 18);    -- 插入年龄必须大于18

   SQL Server报一下错误:

消息 547,级别 16,状态 0,第 1 行
ALTER TABLE 语句与 CHECK 约束"CN_AccountAge18"冲突。该冲突发生于数据库"Nx",表"dbo.Account", column 'Account_Age'。

   这时候我们换一种方式去执行:

alter table Account
    WITH NOCHECK
    add constraint CN_AccountAge18
    check 
    (Account_Age > 18);    -- 插入年龄必须大于18

   以上代码就能够成功执行,并且只有以后添加的数据具备约束,之前添加的不符合条件的数据记录依然存在。

   6.2临时禁用已存在的约束

   当我们需要从另一数据库中导入数据到表中,而表中已建立了约束的时候,可能会存在一些数据和规则不匹配。当然有一个解决方式是先删除约束,添加需要的数据,然后WITH
NOCHECK在添加回去。但是这样做太麻烦了。我们不需要这么做。我们可以采用名为NOCHECK的选项来运行ALTER语句,这样就能够取消需要的约束。

  先来看看上节中创建的这个约束:

alter table Account
    add constraint CN_AccountAge18
    check 
    (Account_Age > 18);    -- 插入年龄必须大于18

   要取消以上约束可以这样来:

Alter Table Account
    NOCHECK
    constraint CN_AccountAge18

  执行命令:

  insert into Account values (25,'取消了约束',17)

  执行成功,成功添加了一行数据。

  留意到又能够向表中插入格式不匹配的数据了。

  这里要说明下,如下知道一个约束是否是启用还是禁用呢?sp_helpconstraint命令,当我们执行sp_helpconstraint的时候,会有一列status_enabled显示该约束的启用状态:

  sp_helpconstraint Account

  金沙js娱乐场官方网站 7

   留意到status_enabled列为Disabled说明是禁用的意思。

  当要启用约束时,只需要用将语句中的NO CHECK替换为CHECK就可以了:

Alter Table Account
    CHECK
    constraint CN_AccountAge18

   执行之后,约束又启用了:再来sp_helpconstraint看下:

  金沙js娱乐场官方网站 8

   留意到status_enabled列变成了Enabled。

  status_enabled的两种状态如下:

  Enabled:启用;

  Disabled:禁用;

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

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

 

SQLServer – 约束

NOT NULL约束

约束字段值不为空。

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

③完整性体现了是否真实地反映现实世界

4、CHECK约束  

  CHECK约束约束可以和一个列关联,也可以和一个表关联,因为它们可以检查一个列的值相对于另外一个列的值,只要这些列都在同一个表中以及值是在更新或者插入的同一行中。CHECK约束还可以用于检查列值组合是否满足某一个标准。

  可以像使用where子句一样的规则来定义CHECK约束。CHECK约束条件的示例如下:

目标 SQL
限制Month列为合适的数字 BETWEEN 1 AND 12
正确的SSN格式 LIKE'[0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9]’
限制为一个快递公司的特定列表 IN(‘UPS’,’Fed Ex’,EMS’)
价格必须为正数 UnitPrice >= 0
引用同一行中的另外一列 ShipDate >= OrderDate

  上面给出的列表只是一小部分,而条件实际上市无限多的。几乎所有可以放到where子句的条件都可以放到该约束中。而且和其他选择(规则和触发器)相比,CHECK约束执行速度更快。

  在已存在的表中添加一个CHECK约束:

alter table Account
    add constraint CN_AccountAge
    check 
    (Account_Age > 18);    -- 插入年龄必须大于18

  如果此时视图添加一条不满足的记录,将报如下错误:

  insert into Account values (22,'洪',17)

消息 547,级别 16,状态 0,第 1 行
INSERT 语句与 CHECK 约束"CN_AccountAge"冲突。该冲突发生于数据库"Nx",表"dbo.Account", column 'Account_Age'。
语句已终止。

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

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是否有默认值为当前日期

查询结果如下
金沙js娱乐场官方网站 9

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

查询结果如下
金沙js娱乐场官方网站 10

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

②绑定规则

5、DEFAULT约束

  和所有约束一样,DEFAULT约束也是表定义的一个组成部分,它定义了当插入的新行对于定义了默认约束的列未提供相应数据时该怎么办。可以定义它为一个字面值(例如,设置默认薪水为0,或者设置字符串列为”UNKNOWN”),或者某个系统值(getdate())。

  对于DEFAULT约束,要了解以下几个特性:

  1、默认值只在insert语句中使用-在update语句和delete语句中被忽略。

  2、如果在insert语句中提供了任意值,那就不使用默认值。

  3、如果没有提供值,那么总是使用默认值。

  值得注意的是,update命令的规则由一个例外,如果显示说明使用默认值就是例外。可以通过使用关键字DEFAULT表示更新的值设置为默认值。

  5.1在创建表时定义DEFAULT约束:

金沙js娱乐场官方网站 11😉

create table person
(
    person_id int identity not null
        primary key,
    person_name nvarchar(30) not null
        default '无名氏',
    person_age int not null
)

金沙js娱乐场官方网站 12😉

  在执行语句后:

insert into person (person_age) values(24)

  表中被插入一条记录如下:

  金沙js娱乐场官方网站 13

   5.2在已存在的表上添加DEFAULT约束:

alter table person
    add constraint CN_DefaultName
    default    '无名氏' for person_name
在建表时添加DEFAULT约束
CREATE TABLE table3(
    t3_id VARCHAR(12),
    t3_name VARCHAR(20),
    t3_class VARCHAR(12),
    t3_type VARCHAR(12) DEFAULT '类型1' 
)

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

(3)缺省与规则定义后,可以重复使用,可以绑定到多个列或数据类型上。

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

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

EXEC sp_unbindefault 'date_time';

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

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

特点:

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

                 金沙js娱乐场官方网站 14

①数据库的完整性是指数据的正确性和相容性

UNIQUE约束

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

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

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

 

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

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

 

删除自定义规则

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

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

(1)作为表定义的一部分在创建表时创建。

删除默认值对象
DROP DEFAULT date_today;

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

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

 

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

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

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

           

未解除绑定的规则,如果再次将一个新的规则绑定到列,旧的规则将自动被解除,只有最近一次绑定的规则有效

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

结果如图所示
金沙js娱乐场官方网站 15

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

②FOREIGN
KEY约束

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

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

其中各参数含义如下:

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

Schemas

1 CREATE TABLE Student2(
2        sno   int CONSTRAINT C1 CHECK (sno BETWEEN 10000 AND 99999),
3        sname  CHAR(8)  CONSTRAINT C2 NOT NULL,
4        sage  int  CONSTRAINT C3  CHECK (sage<30),
5        ssex   VARCHAR(2)  CONSTRAINT C4 CHECK (ssex IN ('男', '女')),
6        CONSTRAINT SK PRIMARY KEY(Sno)
7 );    

约束

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

(3)标识列的数据类型

删除Primary Key
ALTER TABLE table1
DROP CONSTRAINT t_idss;

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

Security

(3)如果已有PRIMARY
KEY约束,则可对其进行修改或删除。

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

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

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

3.违约处理

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

语句已终止。

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

例如: exec
sp_bindefault ‘d_grade’, ‘sc.grade’ 

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

l        数据验证约束

(3)FOREIGN
KEY子句中的列数目和每个列指定的数据类型必须和REFERENCES子句中的列相同。

DEFAULT约束

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

CREATE TABLE

⑤IDENTITY约束 

 

 

默认值:

  或使用CREATE
TABLE语句中的DEFAULT子句指定默认值。

 

金沙js娱乐场官方网站 16

Sp_bindrule 规则名 表名.字段名

 

-》》》完整性再阐述

 

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

(2)缺省与规则建立后与列或数据类型产生关联,列和数据类型就具有了缺省与规则的属性。

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

例如: exec sp_bindrule ‘r_grade’,’sc.grade‘ 

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

主要操作:

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

CREATE
RULE语句,其语法格式如下:

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

例:建立学生登记表Student2,要求学号在10000至99999之间,姓名不能取空值,年龄小于30,性别‘男’或‘女’:

触发器的介绍

 

  1. 1

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

    金沙js娱乐场官方网站 17

    END

缺省与规则有以下特点:

from Database Design to Physical Form

(2)主键内的任何列不能为空(null)。

AS

 

  ->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(系统目录).

(5)向表中的现有列添加UNIQUE约束时,默认情况下SQL
Server 2008检查列中的现有数据确保除NULL外的所有值均唯一。

USE 数据库名

(7)UNIQUE约束优先于唯一索引。

Go

①PRIMARY
KEY 约束:

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

①创建规则

l        唯一性约束

CONSTRAINT
<完整性约束条件名>[PRIMARY KEY短语|FOREIGN KEY
短语|CHECK短语]

Integrity(完整性)

使用sp_bindrule存储过程,语法格式为:

l        默认值约束

 

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

④删除规则

Definition 7.1.2 Column Constraint

使用约束优先于使用触发器、规则和默认值

l        标识列

例如: drop default d_grade 

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

  用SQL
Server Management
Studio创建表时在设计表时指定默认值,可以在输入字段名称后,设定该字段的默认值。