多表检查约束

Question

是否可以根据其他两个表中的标准添加检查约束？我有三个表：集合，会话和session_detail。我的目标是从同一集合和相同的会话类型中防止session_detail中多次发生结构。

。

应用我到 sessions 表的函数表是否检查唯一的集合和会话，但没有为唯一的结构。

ALTER FUNCTION [dbo].[checkSessionType](@collection as varchar(max), @type as varchar(max))  
RETURNS int  
AS   
BEGIN  
DECLARE @sessionType varchar(max) 
SELECT @sessionType = case when @type = 'R' then @type When @type = 'T' then @type END
DECLARE @retval int
SELECT @retval = COUNT(*) 
FROM (SELECT a.structure_id, b.collection, b.session_type 
        FROM session_detail a 
        INNER JOIN sessions b ON a.session_id = b.session_id 
        WHERE b.collection = @collection 
        AND b.session_Type = @sessionType 
) AS tempResults
RETURN @retval  
END;   

这是表格的简化版本：

collections(collection)
sessions(session_id, collection, session_type)
session_detail(detail_id, session_id, structure_id)

那么，只要有不同的会话类型，我将如何允许结构在同一集合中不超过一次？我可以用触发器来做到这一点，但我更喜欢约束。

Answer 1

您可以使用索引视图，例如：

CREATE VIEW v_session_detail
WITH SCHEMABINDING AS
SELECT sd.structure_id, s.collection, s.session_type
FROM dbo.session_detail sd
INNER JOIN dbo.sessions s ON s.session_id=sd.session_id
GO
CREATE UNIQUE CLUSTERED INDEX PK_v_session_detail ON v_session_detail(structure_id, collection, session_type)

Answer 2

如果要控制作为检查约束：

CREATE FUNCTION [dbo].[checkSessionType](@session_id as INT, @structure_id as INT)  
RETURNS int  
AS   
BEGIN  
DECLARE @retval int
SELECT @retval = COUNT(*)
FROM sessions a 
INNER JOIN session_detail b ON a.session_id = b.session_id 
WHERE b.structure_id = @structure_id 
AND b.session_id = @session_id
GROUP BY a.collection, a.session_type, b.structure_id
RETURN @retval  
END
GO
ALTER TABLE [session_detail] ADD CONSTRAINT ck_session_detail CHECK ([dbo].[checkSessionType](session_id, structure_id) <= 1)

同样，您还需要在sessions表上进行检查。为此，您可以创建另一个功能，只是为了检查 sessions表或自定义上一个函数以适合两种情况：

CREATE FUNCTION [dbo].[checkSessionType](@session_id as INT, @collection as INT, @structure_id as INT)  
RETURNS int  
AS   
BEGIN  
DECLARE @retval int
SELECT @retval = COUNT(*)
FROM sessions a 
INNER JOIN  session_detail b ON a.session_id = b.session_id 
WHERE b.structure_id = ISNULL(@structure_id, b.structure_id)
AND a.collection = ISNULL(@collection, a.collection)
AND b.session_id = @session_id
GROUP BY a.collection, a.session_type, b.structure_id
RETURN @retval  
END
GO
ALTER TABLE [sessions] ADD CONSTRAINT ck_sessions CHECK ([dbo].[checkSessionType](session_id, collection, null) <= 1)
ALTER TABLE [session_detail] ADD CONSTRAINT ck_session_detail CHECK ([dbo].[checkSessionType](session_id, null, structure_id) <= 1)