今天又看到一个问题:
SQL语句,约束不重叠。
电影院订票,
一行记录是‘陈’,12,19。
(陈的人订了12到19号座位)
那么下一个记录不能重叠,
即下一条记录不能出现12到19中任何数(13到18都不能再出现),
这个约束怎么写啊?
表结构
Create table reservations(
reserver char(10) not null primary key,
start_seat integer not null,
Finish_seat integer not null
);
go
首先确定检查的条件。
IF EXISTS (
SELECT
1
FROM
reservations r
WHERE
r.start_seat <= 新插入的数据
and r.finish_seat >= 新插入的数据
)
这里的 新插入的数据 ,就是 开始位置 与 结束位置。都要检查
例如
一行记录是‘陈’,12,19
那么新插入一条 ‘李’,1,9 的时候, 1要检查,9也要检查。
假如用触发器来做检查:
CREATE TRIGGER tr_iu_reservation ON reservations
FOR INSERT, UPDATE AS
BEGIN
IF EXISTS (
SELECT
1
FROM
-- 数据库的表 与 本次插入/更新的数据作关联.
-- 但是排除掉 本次自己更新的这些数据.
reservations r
JOIN inserted i ON (r.reserver != i.reserver)
WHERE
( -- 检查 开始.
r.start_seat <= i.start_seat
and r.finish_seat >= i.start_seat )
OR
( -- 检查 结束.
r.start_seat <= i.finish_seat
and r.finish_seat >= i.finish_seat )
)
-- 如果存在重复的数据,那么回滚掉.
ROLLBACK;
END
go
插入测试数据.
INSERT INTO reservations VALUES ('张三', 1, 2);
INSERT INTO reservations VALUES ('陈', 12, 19);
GO
-- 开始插入 检查不通过的数据.
1> INSERT INTO reservations VALUES ('李四', 11, 12);
2> GO
消息 3609,级别 16,状态 1,服务器 HOME-BED592453C\SQLEXPRESS,第 1 行
事务在触发器中结束。批处理已中止。
1> INSERT INTO reservations VALUES ('李四', 19, 20);
2> GO
消息 3609,级别 16,状态 1,服务器 HOME-BED592453C\SQLEXPRESS,第 1 行
事务在触发器中结束。批处理已中止。
1> INSERT INTO reservations
2> SELECT '李四', 19, 20 UNION ALL
3> SELECT '王五', 21, 22
4> GO
消息 3609,级别 16,状态 1,服务器 HOME-BED592453C\SQLEXPRESS,第 1 行
事务在触发器中结束。批处理已中止。
假如要使用约束来处理
那么需要创建一个 FUNCTION 来做数据检查
然后通过创建约束的方式来实现。
先删除掉前面那个触发器
DROP TRIGGER tr_iu_reservation;
创建函数.
CREATE FUNCTION reserExistsCheck(
@reserver char(10),
@start_seat INT,
@finish_seat INT
)
RETURNS INT
AS
BEGIN
-- 检查的结果.
DECLARE @CheckResult INT;
-- 开始检查:
IF EXISTS (
SELECT
1
FROM
reservations r
WHERE
r.reserver != @reserver
AND
(
( -- 检查 开始.
r.start_seat <= @start_seat
and r.finish_seat >= @start_seat )
OR
( -- 检查 结束.
r.start_seat <= @finish_seat
and r.finish_seat >= @finish_seat )
)
)
BEGIN
-- 存在有重复的数据.
SET @CheckResult = 1;
END
ELSE
BEGIN
-- 不存在有重复的数据.
SET @CheckResult = 0;
END
-- 处理完毕. 返回.
RETURN @CheckResult;
END;
GO
-- 为表新增加约束.
ALTER TABLE reservations
ADD CONSTRAINT reservations_check
CHECK (dbo.reserExistsCheck(reserver, start_seat, finish_seat) = 0);
GO
-- 开始插入 检查不通过的数据.
1> INSERT INTO reservations VALUES ('李四', 11, 12);
2> GO
消息 547,级别 16,状态 1,服务器 HOME-BED592453C\SQLEXPRESS,第 1 行
INSERT 语句与 CHECK 约束"reservations_check"冲突。该冲突发生于数据库"test",表"dbo.reservations"。
语句已终止。
1> INSERT INTO reservations VALUES ('李四', 19, 20);
2> GO
消息 547,级别 16,状态 1,服务器 HOME-BED592453C\SQLEXPRESS,第 1 行
INSERT 语句与 CHECK 约束"reservations_check"冲突。该冲突发生于数据库"test",表"dbo.reservations"。
语句已终止。
1> INSERT INTO reservations
2> SELECT '李四', 19, 20 UNION ALL
3> SELECT '王五', 21, 22
4> GO
消息 547,级别 16,状态 1,服务器 HOME-BED592453C\SQLEXPRESS,第 1 行
INSERT 语句与 CHECK 约束"reservations_check"冲突。该冲突发生于数据库"test",表"dbo.reservations"。
语句已终止。
需要注意的地方:
触发器 与 约束,都要增加一个 排除自己这条记录的条件。
因为 SQL Server 的触发器,是 AFTER 的,也就是数据先记录到表里面以后,再触发的。
如果不排除掉自己这一行记录,那么重复的检查,就肯定能检查到结果。
约束也是一样。数据先到表里面,然后再检查。