sql 2005中的各种约束

主键和外键:

例:在表Province_Tb1的同时创建主键ProvinceId和外键AreaID

create table[dbo].[Province_Tb1](

[ProvinceId] [int] IDENTETY(1,1) NOT NULL,
[AreaId] [int] NULL,
[ProvinceName] [varchar] (50) NULL,
constraint(创建一个约束) [PK_Province_TB1] PRIMARY(主键约束) KEY CLUSTERED
(
   [ProvinceId] ASC
)
)ON [PRIMARY]

约束:1.非空约束(NOT NULL)
例:如修改表Province_Tb1的列ProvinceName
   alter table Province_Tb1
   alter column ProvinceName varchar(10) NOT NULL
2.唯一约束(UNIQUE)
可以在新建索引表内勾选"唯一"
编写代码中例:为表Promotor_Tb1指定列PromotorId和StoreId组合唯一的约束，确保PromotorId和StroeId的组合值唯一
Create table [dbo] [Promotor_Tb1](
  [PromotorId] [varchar](20) NOT NULL,
  [PromotorName] [varchar](10) NOT NULL,
  [JoinDate] [datetime] NULL,
  [storeID] [datetime] NULL,
  [stats] [int] NOT NULL,
  [ChangDate] [datetime] NULL,
  Constraint [UQ_Pronotor_Tb1] UNIQUE/VONCLUSTERED
  (
    [PromotorID] ASC,
    [StoreID] ASC,
  )
)

 

(CHECK)约束：
例：限定表Promotor_Tb1中PromotorId列的值在1-500之间
约束表达式：Promotor_Tb1>=1 AND PromotorId<=500
T-SQL语句为:
alter table Promotor_Tb1
with nocheck add conxtraint ck_PromotorId
check (Promotor_Tb1>=1 AND PromotorId<=500)