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

结果如图所示
图片 1

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

例如:  

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

  图片 2

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

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

Alter Table Account
    CHECK
    constraint CN_AccountAge18

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

  图片 3

   留意到status_enabled列变成了Enabled。

  status_enabled的两种状态如下:

  Enabled:启用;

  Disabled:禁用;

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

默认值:

DBMS若发现用户的操作违背了完整性约束条件,就采取一定的动作以保证数据的完整性,如拒绝执行该操作,或级联执行其他操作。

二、约束命名

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

  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、满足以上两个条件的情况下简化名称。

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

           图片 4

 

9、如何选择

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

限制 优点 缺点
约束

快速

可以引用其他列

在命令执行前发生

遵循ANSI标准

必须对每个表重新定义

不能引用其他表

不能绑定到数据类型

规则

独立的对象

可重用

可以绑定到数据类型

命令执行前发生

稍慢

不能跨列使用

不能引用其他表

实际上只用于向后兼容

默认值

非常灵活

可以引用其他列或其他表

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

在命令执行之后发生

系统开销很大

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

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

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

NOT NULL约束

约束字段值不为空。

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

(1)UNIQUE约束在列集内强制执行值的唯一性。

5、DEFAULT约束

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

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

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

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

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

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

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

图片 5😉

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

图片 6😉

  在执行语句后:

insert into person (person_age) values(24)

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

  图片 7

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

alter table person
    add constraint CN_DefaultName
    default    '无名氏' for person_name

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

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

查询结果如下
图片 8

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

查询结果如下
图片 9

integrity constraints (完整性约束)

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 );    

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] 
) 

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

图片 10😉

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

图片 11😉

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

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

①PRIMARY
KEY 约束

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'。
语句已终止。

约束

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

 

8、触发器

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

删除自定义规则

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

 

 

三、键约束

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

③解除规则的绑定

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提供了依赖于你所查询对象的所有对象列表。

DEFAULT约束

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

USE 数据库名

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

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关键字并且当前表中还没有聚集索引。
  •     每个表中只能有一个主键,但可以由多个唯一约束。
添加约束指定约束名
ALTER TABLE table3
ADD CONSTRAINT df_t3_type
DEFAULT '类型2' FOR t3_type;
GO

Introduction

学生所在的系必须是学校开设的系;

一、约束的分类

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

  1、实体约束

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

  2、域约束

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

  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’。

语句已终止。

CREATE RULE 规则名

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

SQLServer – 约束

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

结果如图所示
图片 12

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

 

2、外键约束

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

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

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

图片 13😉

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

图片 14😉

  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值匹配的行,还是允许插入。

UNIQUE约束

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

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

创建规则

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

删除Primary Key
ALTER TABLE table1
DROP CONSTRAINT t_idss;

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

Sp_bindrule 规则名 表名.字段名

 

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

                    触发器

 

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

AS 常亮表达式

 

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

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

②使用CREATE
DEFAULT语句创建默认对象后,使用存储过程sp_bindefault将该默认对象绑定到列上。

CHECK约束

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

-》》》完整性再阐述

①PRIMARY
KEY 约束:

删除默认值对象
DROP DEFAULT date_today;

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

触发器的介绍

 

  1. 1

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

    图片 15

    END

⑤IDENTITY约束 

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

Security

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

主关键字约束(Primary Key Constraint)

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

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

(2)它指定应用于列中输入的所有值的布尔(取值为TRUE或FALSE)搜索条件,拒绝所有不取值为TRUE的值。

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。

图片 16 

⑥IDENTITY
约束

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

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

一、数据库完整性概述

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

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

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

使用FOREIGN
KEY约束,应注意的几个问题:

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

       CHECK

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

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

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

EXEC sp_unbindefault 'date_time';

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

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

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

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

结果如图所示
图片 17

                      图片 18

④查看默认对象

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

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

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

Schemas

③解除默认值对象的绑定

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

触发器步骤详解

 

  1. 1

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

    create trigger mytrigger on AddTable
    for update

    图片 19

  2. 2

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

    图片 20

  3. 3

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

    图片 21

  4. 4

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

    图片 22

  5. 5

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

    图片 23

  6. 6

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

    图片 24

  7. 7

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

    图片 25

  8. 8

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

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

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

    图片 26

  9. 9

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

    图片 27

  10. 10

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

    图片 29

  11.  

 图片 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

图片 65

图片 66

图片 67

图片 68

图片 69

图片 70

 

(2)如果现有表的某列与另一个表已有的PRIMARY
KEY约束或UNIQUE约束相关联,则可向现有表添加FOREIGN KEY约束。

The GRANT & REVOKE statements

 

from Database Design to Physical Form

 

Definition 7.1.2 Column Constraint

sp_unbindrule
[@objname = ] ‘object name’  [,[@futureonly
= ] ‘futureonly_ lag’]

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

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

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

 

l        默认值约束

规则是当用户进行INSERT或uPDATE操作时,对输入列中的数据设定的取值范围,是实现域完整性的方式之一。

 

 

       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.       

 

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

 

Catalogs

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

 

②提供完整性检查的方法

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

缺省和规则来源于由Sybase开发的S默认值QL
Server,在老版本的SQL Server或者升级版本中都有缺省和规则的使用。

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

其中: [,
[@futureonly = ]
‘futureonly_flag’]仅在此之后将默认值绑定到用户定义的数据类型时才使用。

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

默认对象创建后不能使用,必须首先将其绑定到某列或者用户自定义的数据类型上。其使用语法格式如下:

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

(3)对已有的FOREIGN
KEY约束进行修改或删除。

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

④查看默认值对象

       EXAMPLE

(4)缺省与规则不随表同时调入内存,当用到时才被调入内存,这可能会使程序执行出现延时。

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

rulename  是新规则的名称。

                 图片 71

①创建默认对象

l        外键约束

 

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

 

 

(2)标识增量

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

 

           

⑤NOT
NULL(非空性)

Author

发表评论

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