SQL2005中的几种约束

约束（constraint）：约束是SQL Server提供的自动保持数据库完整性的一种方法，
它通过限制字段中数据、记录中数据和表之间的数据来保证数据的完整性。
在SQL SERVER中，对于基本表的约束分为列约束和表约束。

（要想看列约束和表约束的区别，请看检查约束的例子。）

列约束是对某一个特定列的约束，包含在列定义中，直接跟在该列的其他定义之后，用空格分隔，
不必指定列名；

表约束与列定义相互独立，不包括在列定义中，通常用于对多个列一起进行约束，与列定义用’,’分隔，
定义表约束时必须指出要约束的那些列的名称。

1）创建约束

完整性约束的基本语法格式为：
   [CONSTRAINT constraint_name（约束名）] <约束类型>
   约束不指定名称时，系统会给定一个名称。

在SQL Server 2005中有6种约束：
主键约束（primary key constraint）、
惟一性约束（unique constraint）、
检查约束（check constraint）、
默认约束（default constraint）、
外部键约束（foreign key constraint）
空值（NULL）约束。

创建检查约束常用的操作方法有如下两种 ：
使用SQL Server管理平台创建检查约束（即右键，选择，点击的过程）；
用Transact-SQL语句创建检查约束。

2）检查约束 CHECK

检查约束对输入列或者整个表中的值设置检查条件，以限制输入值，保证数据库数据的完整性。

这里全选sql语句写的约束做例子。

用Transact-SQL语句创建检查约束。其语法形式如下：
   CONSTRAINT constraint_name
   CHECK [NOT FOR REPLICATION]
   (logical_expression)

例（注：下面为列约束）
CREATE TABLE 我的会员
(
会员编号 smallint NOT NULL CONSTRAINT MemberNoChk
CHECK (会员编号 BETWEEN 1 AND 10000),
姓名 nvarchar(20) NOT NULL,
地址 nvarchar(60) NOT NULL
);
GO

（注：下面为表约束）

看清楚了没有：列约束和表约束的区别就是列约束是直接跟在后面，而表约束则不是。

CREATE TABLE 我的会员
(
会员编号 smallint NOT NULL,
姓名 nvarchar(20) NOT NULL,
地址 nvarchar(60) NOT NULL,
CONSTRAINT ChkMemberNo
CHECK (会员编号 BETWEEN 1 AND 10000)
);
GO

2）主键约束PRIMARY KEY

PRIMARY KEY约束用于定义基本表的主键，它是惟一确定表中每一条记录的标识符，
其值不能为NULL，也不能重复，以此来保证实体的完整性。
PRIMARY KEY与UNIQUE约束类似，通过建立唯一索引来保证基本表在主键列取值的唯一性，
但它们之间存在着很大的区别：

①在一个基本表中只能定义一个PRIMARY KEY约束，但可定义多个UNIQUE约束；

②对于指定为PRIMARY KEY的一个列或多个列的组合，其中任何一个列都不能出现空值，
而对于UNIQUE所约束的唯一键，则允许为空。

注意：不能为同一个列或一组列既定义UNIQUE约束，又定义PRIMARY KEY约束。
PRIMARY KEY既可用于列约束，也可用于表约束。

使用Transact-SQL语句操作法设置主键约束，其语法形式如下：
   CONSTRAINT constraint_name
   PRIMARY KEY [CLUSTERED|NONCLUSTERED]
（column_name）

PRIMARY KEY用于定义表约束时，即将某些列的组合定义为主键，其语法格式如下：
   CONSTRAINT constraint_name
   PRIMARY KEY [CLUSTERED|NONCLUSTERED]
（column_name[,…n]）

3）惟一性约束UNIQUE

使用Transact-SQL语句完成惟一性约束的操作，其语法形式如下：
CONSTRAINT constraint_name
UNIQUE [CLUSTERED|NONCLUSTERED]
（column_name[,…n]）

CREATE TABLE 我的员工
(
员工编号 int IDENTITY(10,10) NOT NULL CONSTRAINT PK_员工编号_我的员工 PRIMARY KEY ,
身份证号码 char(10) NOT NULL CONSTRAINT UQ_身份证号码_我的员工 UNIQUE,
技能检定编号 char(6) NOT NULL CONSTRAINT UQ_技能检定编号_我的员工 UNIQUE,
姓名 nvarchar(16) NULL,
电话号码 char(11) NULL,
行动电话 int NULL,
CONSTRAINT UQ_姓名_电话号码_我的员工 UNIQUE (姓名, 电话号码),
CONSTRAINT UQ_姓名_行动电话_我的员工 UNIQUE (姓名, 行动电话)
);

4）外部键约束FOREIGN KEY

     外键 (FOREIGN KEY) 是用于建立和加强两个表数据之间的链接的一列或多列。
外部键约束用于强制参照完整性。
      当使用外部键约束时，应该考虑以下几个因素：
外部键约束提供了字段参照完整性；
外部键从句中的字段数目和每个字段指定的数据类型都必须和REFERENCES从句中的字段相匹配；
外部键约束不能自动创建索引，需要用户手动创建；
用户想要修改外部键约束的数据，必须有对外部键约束所参考表的SELECT权限或者REFERENCES权限；
参考同一表中的字段时，必须只使用REFERENCES子句，不能使用外部键子句；
一个表中最多可以有253个外部键约束；
在临时表中，不能使用外部键约束；
主键和外部键的数据类型必须严格匹配

5）默认约束 DEFAULT

默认约束指定在插入操作中如果没有提供输入值时，则系统自动指定值。
默认约束可以包括常量、函数、不带变元的内建函数或者空值。

创建默认约束的Transact-SQL语句操作法。其语法形式如下：
CONSTRAINT constraint_name
   DEFAULT constraint_expression [FOR column_name]

CREATE TABLE 我的员工
（
地址 varchar(60) NOT NULL DEFAULT '北京市',
年收入 money NULL DEFAULT 10000 ）
GO

6）空值约束NULL | NOT NULL

空值（NULL）约束用来控制是否允许该字段的值为NULL。NULL值不是0也不是空白，
更不是填入字符串的“NULL”字符串，而是表示“不知道”、“ 不确定”或“没有数据”的意思。
当某一字段的值一定要输入才有意义的时候，则可以设置为NOT NULL。
如主键列就不允许出现空值，否则就失去了唯一标识一条记录的作用。
空值（NULL）约束只能用于定义列约束。

使用Transact-SQL语句设置空值（NULL）约束， 其语法形式如下：
     [CONSTRAINT <约束名> ][NULL|NOT NULL]

建立一个S表，对SNO字段进行NOT NULL约束。
程序清单如下：

CREATE TABLE S
(SNO CHAR(10) CONSTRAINT S_CONS NOT NULL,
SN VARCHAR(20),
AGE INT,
SEX CHAR(2) DEFAULT '男' ,
..................

----------------------------
约束主要包括：
主键约束（primary key constraint）、
惟一性约束（unique constraint）、
检查约束（check constraint）、
默认约束（default constraint）、
外部键约束（foreign key constraint）
空值（NULL）约束。

1、not null ：用于控制字段的内容一定不能为空（NULL）。
     用法 ：Create table MyTable
               (
                   id varchar(32) not null,
                   name varchar (32)
               )

2、Unique ：控件字段内容不能重复，一个表允许有多个 Unique 约束。
      在Sql Server、Orcale、MS Access 支持的添加 Unique 语法：
             Create table MyTable
                (
                    id varchar(32) not null unique,
                    name varchar (32)
                )

      在Sql Server、 My Sql 支持的添加 Unique 语法：
             Create table MyTable
                (
                    id varchar(32) not null,
                    name varchar (32),
                   unique (id,.....)
                 )

      在Sql Server、Orcale、MS Access、My Sql 都支持的添加 Unique 语法：
              Create table MyTable
                  (
                      id varchar(32) not null,
                      name varchar (32),
                     Constraint uniqueName unique(UniqueColumn,.....)
                   )

      注：第一种写法（也就是在字段后面直接添加约束）,保证每个字段数据唯一性。
          第二种写法（也就是同时添加几个字段为约束），这个是保证几个字段数据同时是唯一的，比如 Unique(id,name) 两个字段为约束，那么当 id 有重复值，而 name 没有重复值的情况下是允许的，只有当两个字段数据都与原数据重复的情况下才是不允许的。
  
   在Sql Server、Oracle、MS Access 删除 Unique 约束语法：drop constraint UniqueName;
   在My Sql 删除 Unique 约束语法：drop index UniqueName;

3、Primary Key ：也是用于控件字段内容不能重复，但它在一个表只允许出现一个。
      在Sql Server、Orcale、MS Access 支持的添加Primary Key语法：
              Create table myTB1
             (
                 id nvarchar(32) not null primary key,
                 name nvarchar(32)
              )
      在Sql Server、My Sql 支持的添加Primary Key 语法：
             Create table myTB1
             (
                 id nvarchar(32) not null,
                 name nvarchar(32),
                 primary key (id)
             )
       在Sql Server、Orcale、MS Access、My Sql 支持的添加Primary Key 语法：
             Create table myTB1
             (
                  id nvarchar(32) not null,
                 name nvarchar(32),
                 constraint PrimaryName primary key (id)
              )
在Sql Server、Orcale、MS Access、My Sql 表已存在的情况下，添加表的Primary Key约束语法：
              Alter table myTB1
              ADD Primary Key (id,......) --这样的写法，系统会自定义约束名称

              Alter table myTB1
              Add Constaint PrimaryName primary key (id) --这样的写法，自己可以自定义约束名称

在Sql Server、Orcale、MS Access 删除表已存在的 Primary Key 约束的语法：
              Alter table myTB1
              Drop Constraint PrimaryName

在My Sql 删除表已存在的 Primary Key 约束的语法：
              Alter table myTB1
              Drop Primary Key
Unique 与 Primary 的相同之处：UNIQUE 和 PRIMARY KEY 约束均为列或列集合提供了唯一性的保证。
Unique 与 Primary 的不同之处：每个表可以有多个 UNIQUE 约束，但是每个表只能有一个 PRIMARY KEY 约束，Unique允许有NULL值，而 Primary key 不允许有NULL值。

注：在同一个数据库中，就算在不同的表中，约束名称是不允许相同的。

4、Foreign Key ：FOREIGN KEY 约束用于预防破坏表之间连接的动作，FOREIGN KEY 约束也能防止非法数据插入外键列，因为它必须是它指向的那个表中的值之一。
   在Sql Server、My Sql 支持的添加Foreign Key语法：
              Create table myTB1
              (
                   id nvarchar(32) not null primary key,
                   name nvarchar(32),
                   foreign key(id) references myTB(id)
              )

   在Sql Server、Orcale、MS Access 支持的添加Foreign Key语法：
              Create table myTB1
              (
                   id nvarchar(32) not null foreign key references myTB(id),
                   name nvarchar(32)
              )

   在Sql Server、Orcale、MS Access、My Sql 都支持的添加Foreign Key语法：
              Create table myTB1
              (
                   id nvarchar(32) not null primary key,
                   name nvarchar(32),
                    Constraint foreignName foreign key(id) references myTB(id)
              )

在Sql Server、Orcale、MS Access、My Sql 的表已存在情况下，向表添加外键约束的语法：
              Alter table myTB1
              Add foreign key(id) references myTB(id) --这样写系统会自定义约束名称

               Alter table myTB1
              Add Constraint foreignName foreign key(id) references myTB(id) --这样写自己可以自定义约束名称

在Sql Server、Orcale、MS Access 中删除外键约束的语法：
              Alter table myTB1
              Drop Constraint foreignName;

在My Sql 中删除外键约束的语法：
              Alter table myTB1
              Drop foreign key foreignName;

5、Check ：用于控制字段的值范围。
      在Sql Server、My Sql 支持的添加check 语法：
              Create table myCheck
              (
                   id nvarchar(32) not null,
                   age int not null,
                   check (age>15 and age <30)
              )

       在Sql Server、Orcale、MS Access 支持的添加 check 语法：
              Create table myCheck
              (
                   id nvarchar(32) not null,
                   age int not null check (age>15 and age<30)
              )

      在Sql Server、Orcale、MS Access、My Sql 都支持的添加 check 语法：
               Create table myCheck
              (
                   id nvarchar(32) not null,
                   age int not null,
                   constraint checkName check (age<15 and age>30)
              )

在Sql Server、Orcale、MS Access、My Sql 的表已存在情况下，向表添加check约束的语法：
              Alter table myCheck
              add check (id='celly'); --这样定义是系统自定义 check约束名称。

              Alter table myCheck
               add constraint checkName check(id='celly'); --这样定义是自己自定义 check约束名称。

在 Sql Server、Orcale、MS Access 删除表已存在的 check 约束的语法：
              Alter table myCheck
              drop constraint checkName

6、Default ：用于设置新记录的默认值。

      在Sql Server、Orcale、MS Access、My Sql 添加default约束的语法：
              Create table myDefault
              (
                  id int,
                  name nvarchar(32) default 'celly'
              )

      在My Sql 的已存在表中添加 字段默认值：
               Alter table myDefault
              Alter [id] set default 0

      在 Sql Server、Orcale、MS Access 的已存在表中添加 字段默认值：
              Alter table myDefault
              Alter column [id] set default 0

      在 My Sql 中删除字段默认值语法：
              Alter table myDefault
              Alter ColumnName drop default

本文来自CSDN博客，转载请标明出处：
http://blog.csdn.net/fancy050821/archive/2009/05/13/4176212.aspx

-------------------------------------------
sql server中增加约束的语句是：

---添加主键约束
alter table 表名 add constraint 约束名 primary key (主键)

---添加唯一约束
alter table 表名 add constraint 约束名 unique (字段)

---添加默认约束
alter table 表名 add constraint 约束名 default ('默认内容') for 字段

--添加检查check约束,要求字段只能在1到100之间
alter table 表名 add constraint 约束名 check (字段 between 1 and 100 )

---添加外键约束(主表stuInfo和从表stuMarks建立关系,关联字段为stuNo)
alter table 从表 add constraint 约束名 foreign key(关联字段) references 主表(关联字段)
GO

--sql server中删除约束的语句是：
alter table 表名 drop constraint 约束名

sp_helpconstraint 表名 找到数据表中的所有列的约束
在查询分析器中执行

-----------------------------------------------
SQL SERVER 2005中，约束，Constraints，顾名思义，就是按照约定来束缚某种东西。
在数据库，无非就是对存储的数据进行束缚，当然需要按照某种既定的规则，
一般是指语义的限制以及用来制约属性上的关系。可能在平时的工作中，尤其是对开发人员来说，
约束用的不是很多，更多的时候是用代码去做一些输入的验证，这其实就是约束的思想，
把不满足条件的数据排除在外。其实SQL SERVER 2005也给我们提供这样的一些约束：

1.         Check Constraints

我相信大部分的朋友对约束的概念理解应该都是Check类型的约束，
这种约束对表中的某一列的格式或者值进行限制，只有满足条件的数据才能被写入。比如：

create table Goods
(
   GoodsID int identity(1,1) primary key,
   Price decimal(12,4) constraint chkPrice check (Price>1000)
)

上面的SQL建立了一张新表，并且对表中的列Price建立一个check约束，名叫chkPrice，
它的作用就是对插入price列的数据进行判断，如果大于1000，满足条件就插入，否则就报错。

insert into Goods values(2000)
--(1 row(s) affected) 成功

insert into Goods values(200)
--ERROR：The INSERT statement conflicted with the CHECK constraint "chkPrice". T
--he conflict occurred in database "AdventureWorks", table "dbo.Goods", column 'Price'.

这里顺便提一下，如果我们执行了下面的代码，结果会是怎样呢？
insert into goods values(null)

如果你觉得也会报错，那么恭喜你，你错了。约束其实是对满足条件，
即条件表达式返回TRUE的一律通过。那么，如果结果是NULL呢？NULL在SQL SERVER里的类型是UNKNOWN
，就是不知道。那我们就要把它理解成可以成为任何类型，可能为TRUE。所以在这个例子中，
NULL是可以被插入的。

2.         Default Constraints
 这个约束相对简单一些，就是事先定义一个默认值，如果在插入数据的时候没有指定，
系统会自动把事先定义好的默认值写入对应的列。

create table Product(
ProductID int identity(1,1) primary key,
ProductName varchar(80) not null,
Country varchar(40) default 'China'
)
Country这列就是一个含有Default约束的列，如果在插入一条数据的时候没有指定值，
那么系统会默认的插入China.

insert into product values('AAA','US')
insert into product(ProductName) values('BBB')
--结果为
--      1 AAA US
--
--      2 BBB China

3.         Unique Constraints

     Unique就是独有的，这个约束就是确保在非主键列中不输入重复的值。
这一点与主键的概念可能有一些类似，之所以把Unique放在前面说，就是为了强调一点，
如果想强制一列或者多列的组合的唯一性时，应该选择Unique而不是主键。
     同时，还有一点，主键不能为null，但是Unique可以支持null。

alter table product  add ProductCode int null constraint pc_unique unique
我们对Product表中的ProductCode列设置了unique约束,这一列的值不可重复.但允许有空值存在.

insert into product (productname,ProductCode) values('aaa',null)
--OK 第一个空值插入成功

insert into product (productname,ProductCode) values('bbb',null)
--ERROR:Violation of UNIQUE KEY constraint 'pc_unique'.
--Cannot insert duplicate key in object 'dbo.product'.
--第二个空值插入失败

4.         Primary Key Constraints
 主键约束可能是大家最为熟悉的,在数据库的设计中,我们基本上会对每一张表建一个主键约束,
用以把数据库的每条记录(行)区分开来.主键可以针对表中的一列或多列。但与约束不同的是，
它的值不能为空。

下面的例子是对其中一列设置主键：

Create Table Users(
 UserID int primary key,
 UserName varchar(50) not null
)

 下面是对多列设置主键：

CREATE TABLE KeyData
(
    FiscalYear int not null,
    Period int not null,
    DataType int not null,
    KeyValue int not null
    CONSTRAINT [PK_constraint_keydata] PRIMARY KEY CLUSTERED
    (
        FiscalYear,
        Period
    )
)

5.         Foreign Key Constraints
前面提到约束分语义上限制和属性关系的限制，外键约束就是用来限制属性间关系的。
通俗一点讲，就是一张表中的某列引用了另一张表的主键，因为该列的值必须出自主键列的值。

Create table Manager
(
    ManagerID int primary key,
    ManagerName varchar(50),
)
上面我们建立了一个Manager的表，有一个ID列是该表的主键，唯一代表了每一个经理。

下面建立了销售区域的表，并指定每个区域有一个经理负责：
Create Table SalesRegion
(
    SalesRegionID int Primary Key,
    SalesRegionName varchar(100),
    ManagerID int foreign key REFERENCES Manager(ManagerID)
)
从上面的命令中可以看出，ManagerID就是外键，它的作用是连接了SalesRegion与Manager两张表，
限制ManagerID在表SalesRegion中的使用，我们不能把某个区域指定给一个不存在经理负责。
如果我们试图往SalesRegion插入一条记录，而该记录的ManagerID是Manager表中不存在的ManagerID,
系统就会报出以下的错误：
--The INSERT statement conflicted with the FOREIGN KEY constraint
--"FK__SalesRegi__Manag__3F6663D5". The conflict occurred in database "AdventureWorks",
--table "dbo.Manager", column 'ManagerID'.