--
--3.4.4节示例
--
--UNIQUE列约束
CREATE TABLE [Admin]
(
AdminID int PRIMARY KEY IDENTITY,
LoginName varchar(50) NOT NULL UNIQUE, --指定该列为唯一约束的列
Password varchar(50) NOT NULL
)
--UNIQUE表约束
CREATE TABLE [Admin]
(
AdminID int PRIMARY KEY IDENTITY,
LoginName varchar(50) NOT NULL,
Password varchar(50) NOT NULL,
CONSTRAINT UNIQUE_LoginName UNIQUE(LoginName) --以单独约束的方式指定唯一约束
)
--修改表添加UNIQUE约束
ALTER TABLE [Admin]
ADD CONSTRAINT UNIQUE_LoginName UNIQUE(LoginName) --修改表增加唯一约束
--
--3.4.5节示例
--
--PRIMARY KEY列约束
CREATE TABLE [Admin]
(
AdminID int PRIMARY KEY IDENTITY, --设置该列为主键
LoginName varchar(50) NOT NULL,
Password varchar(50) NOT NULL
)
--PRIMARY KEY表约束
CREATE TABLE [Admin]
(
AdminID int IDENTITY,
LoginName varchar(50) NOT NULL,
Password varchar(50) NOT NULL,
CONSTRAINT PK_AdminID PRIMARY KEY(AdminID) --单独以约束方式设置主键
)
--修改表的主键
ALTER TABLE Admin
DROP CONSTRAINT PK_Admin --删除原有主键约束
GO
ALTER TABLE Admin
ADD CONSTRAINT PK_LoginName --增加新的主键约束
PRIMARY KEY (LoginName)
--
--3.4.6节示例
--
--创建FOREIGN KEY列约束
CREATE TABLE Department --创建部门表
(
DepartmentID int IDENTITY PRIMARY KEY,
DepartmentName nvarchar(20) NOT NULL
)
GO
CREATE TABLE Employee --创建员工表
(
EmployeeID int IDENTITY PRIMARY KEY,
EmployeeName nvarchar(10) NOT NULL,
DepartmentID int NOT NULL
FOREIGN KEY REFERENCES Department(DepartmentID) --外键约束
)
--创建FOREIGN KEY表约束
CREATE TABLE Department
(
DepartmentID int IDENTITY PRIMARY KEY,
DepartmentName nvarchar(20) NOT NULL
)
GO
CREATE TABLE Employee
(
EmployeeID int IDENTITY PRIMARY KEY,
EmployeeName nvarchar(10) NOT NULL,
DepartmentID int NOT NULL,
CONSTRAINT FK_Employee_Department --单独以约束的方式定义外键约束
FOREIGN KEY(DepartmentID)
REFERENCES Department(DepartmentID)
)
--增加FOREIGN KEY约束
ALTER TABLE Employee
ADD CONSTRAINT FK_Employee_Department
FOREIGN KEY(DepartmentID)
REFERENCES Department --不指定参照列则参照列就是Department的主键DepartmentID
--创建自参照表
CREATE TABLE Department2
(
DepartmentID int IDENTITY PRIMARY KEY,
DepartmentName nvarchar(20) NOT NULL,
ParentID int NULL REFERENCES Department2(DepartmentID) --指定自参照外键约束
)
--创建带基础行的自参照表
CREATE TABLE Department3 --创建无外键约束的表
(
DepartmentID int IDENTITY PRIMARY KEY,
DepartmentName nvarchar(20) NOT NULL,
ParentID int NOT NULL
)
GO
INSERT INTO Department3(DepartmentName,ParentID) –添加基础行
VALUES('虚拟部门',1)
GO
ALTER TABLE Department3 --添加外键约束
ADD CONSTRAINT FK_Department_Department
FOREIGN KEY (ParentID) REFERENCES Department3(DepartmentID)
--定义级联删除
CREATE TABLE Department
(
DepartmentID int IDENTITY PRIMARY KEY,
DepartmentName nvarchar(20) NOT NULL
)
GO
CREATE TABLE Employee
(
EmployeeID int IDENTITY PRIMARY KEY,
EmployeeName nvarchar(10) NOT NULL,
DepartmentID int NOT NULL,
CONSTRAINT FK_Employee_Department --定义外键约束
FOREIGN KEY(DepartmentID)
REFERENCES Department(DepartmentID)
ON UPDATE NO ACTION --更新时不执行操作
ON DELETE CASCADE --删除时进行级联删除
)
--
--3.4.7节示例
--
--创建CHECK列约束
CREATE TABLE [User]
(
UserID int IDENTITY PRIMARY KEY,
UserName nvarchar(10) NOT NULL,
Age int CHECK(Age>=0 AND Age<=120) --指定该列的CHECK约束
)
--创建CHECK表约束
CREATE TABLE Sick
(
SickID int IDENTITY PRIMARY KEY,
SickName nvarchar(5) NOT NULL,
BloodType varchar(2) NOT NULL,
CONSTRAINT CK_BloodType CHECK(BloodType IN ('A','B','AB','O'))
--以单独约束方式定义CHECK约束
)
--创建多列比较CHECK约束
CREATE TABLE Meeting
(
MID int IDENTITY PRIMARY KEY,
Topic nvarchar(50) NOT NULL,
StartTime datetime NOT NULL,
EndTime datetime NOT NULL,
CONSTRAINT CK_Time CHECK(StartTime<EndTime) --多个列之间的CHECK约束
)
--
--3.4.8节示例
--
--创建并绑定规则
CREATE TABLE Sick
(
SickID int IDENTITY PRIMARY KEY,
SickName nvarchar(5) NOT NULL,
BloodType varchar(2) NOT NULL
)
GO
CREATE RULE BloodType --创建规则
AS
@type IN ('A','B','AB','O')
GO
EXEC sp_bindrule 'BloodType','Sick.BloodType' --绑定规则
--
--3.4.9节示例
--
--创建并使用默认值
CREATE TABLE [Users]
(
id int IDENTITY PRIMARY KEY,
UserName nvarchar(10) NOT NULL,
Age int NOT NULL
)
GO
CREATE DEFAULT Default20 –创建默认值
AS 20
GO
EXEC sp_bindefault 'Default20','Users.Age' –绑定默认值
--
--3.4.10节示例
--
--创建Product表
CREATE TABLE Product
(
Pid int IDENTITY PRIMARY KEY,
ProductName nvarchar(50) NOT NULL,
ProductCode varchar(10) NOT NULL
)
GO --创建表，然后插入数据
INSERT INTO Product(ProductName,ProductCode)
VALUES('产品A','C01001');
INSERT INTO Product(ProductName,ProductCode)
VALUES('产品B','A01051');
--使用WITH NOCHECK创建约束
ALTER TABLE Product
WITH NOCHECK --不检查历史数据
ADD CONSTRAINT CK_Product CHECK(ProductCode LIKE 'SN%')
GO
INSERT INTO Product(ProductName,ProductCode)
VALUES('产品C','SS01001');--不满足约束，插入失败
GO
INSERT INTO Product(ProductName,ProductCode)
VALUES('产品D','SN01051');--满足约束，插入成功
GO
--关闭约束
CREATE TABLE Product --创建带有约束的产品表
(
Pid int IDENTITY PRIMARY KEY,
ProductName nvarchar(50) NOT NULL,
ProductCode varchar(10) NOT NULL ,
CONSTRAINT CK_Product CHECK(ProductCode LIKE 'SN%')
)
GO
INSERT INTO Product(ProductName,ProductCode)
VALUES('产品A','01001'); --不符合约束的数据无法插入
GO
ALTER TABLE Product
NOCHECK CONSTRAINT CK_Product –禁用约束
GO
INSERT INTO Product(ProductName,ProductCode)
VALUES('产品A','01001'); --不符合约束的数据允许插入
ALTER TABLE Product
CHECK CONSTRAINT CK_Product --启用约束
