sql基础语法-创建表和约束

创建数据库表

USE SQL2016
IF OBJECT_ID('dbo.Employees','U') IS NOT NULL
DROP TABLE dbo.Employees;

Create TABLE dbo.Employees
(
empid INT NOT NULL,
firstname VARCHAR(30) NOT NULL,
lastname VARCHAR(30) NOT NULL,
salary Money Null
);

USE SQL2016
IF OBJECT_ID('dbo.Orders','U') IS NOT NULL
DROP TABLE dbo.Orders;

Create TABLE dbo.Orders
(
orderid INT NOT NULL,
empid INT NOT NULL,
customerid VARCHAR(30) NOT NULL,
qty INT NOT Null,
orderdatetime DATETIME2 NOT NULL,
CONSTRAINT PK_Orders--建立表时，直接创建主键约束
PRIMARY KEY (orderid)
);

--表已经存在，创建主键约束
ALTER TABLE dbo.Employees
ADD CONSTRAINT PK_Employees
PRIMARY KEY(empid);

--唯一约束
ALTER TABLE dbo.Employees 
ADD CONSTRAINT UNQ_Employees_lastname
UNIQUE(lastname);


--外键约束
ALTER TABLE dbo.Orders
ADD CONSTRAINT FK_Orders_Employees
FOREIGN KEY(empid)
REFERENCES dbo.Employees(empid);

--CHECK约束
ALTER TABLE dbo.Employees
Add CONSTRAINT CHK_Employees_Salary
CHECK(salary>0.00);

--默认约束
ALTER TABLE dbo.Orders
ADD CONSTRAINT DFT_Orders_orders
DEFAULT(SYSDATETIME()) FOR orderdatetime

 创建测试数据

--创建测试数据

--以下两段脚本创建了 两个employee 

USE [SQL2016]
GO

INSERT INTO [dbo].[Employees]
([empid]
,[firstname]
,[lastname]
,[salary])
VALUES
(1,'zhang','san',5000)
GO

INSERT INTO [dbo].[Employees]
([empid]
,[firstname]
,[lastname]
,[salary])
VALUES
(2,'li','si',6000)
GO 

--以下脚本创建了两个orders

USE [SQL2016]
GO

INSERT INTO [dbo].[Orders]
([orderid]
,[empid]
,[customerid]
,[qty])
VALUES
(1,1,'CUS1',10)
GO

INSERT INTO [dbo].[Orders]
([orderid]
,[empid]
,[customerid]
,[qty])
VALUES
(2,2,'CUS2',20)
GO

 

验证约束条件

验证外键约束，empid为3的employee不存在，故以下脚本报错

USE [SQL2016]
GO

INSERT INTO [dbo].[Orders]
([orderid]
,[empid]
,[customerid]
,[qty])
VALUES
(3,10,'CUS1',10)
GO

 

验证外键约束，删除主表记录，主表中的值被其他表引用，empid=1的记录在order中被引用，故删除错误

USE [SQL2016]
DELETE FROM dbo.Employees WHERE empid=1
GO

 

验证外键约束，UPDATE主表记录，主表中的值被其他表引用，empid=1的记录在order表中被引用，故更新失败

USE [SQL2016]
UPDATE dbo.Employees set empid=4 where empid=1
GO

 

外键约束默认执行 no action引用操作，即delete 和 update 数据时，如果被引用，则默认不允许操作。

 

其他选项：
CASCADE：将级联相关的行
SET DEFAULT：将相关行设置为默认值
SET NULL：将相关行设置为NULL

将Delete Rule 和Update Rule 改为 CASCADE后。

执行以下脚本，将empid=1改为4，删除empid=2的记录。则应该自动修改Orders里的empid=1的改为4，删除empid=2的orders

USE [SQL2016]
GO
UPDATE dbo.Employees set empid=4 where empid=1

DELETE FROM dbo.Employees WHERE empid=2

GO

SELECT * FROM dbo.Orders

 

 

CHECK 约束仅当false时拒绝操作，当为true和unknow时允许操作，例如
-1000，拒绝操作
5000，允许操作
NULL，允许操作
WITH NOCHECK 选项：