复杂的多列唯一约束
问题描述:
我有一个10列的表,但只关心这3列。想象一下,我的表是这样的:复杂的多列唯一约束
CREATE TABLE MyTable (RowID int IDENTITY(1,1), UserID int, NodeID int, RoleID int)
我需要的是一个能够执行下列约束:用户ID和角色ID必须为每个节点ID是唯一的(即用户不能在多个节点的相同角色)。换句话说,我想允许
INSERT MyTable (UserID, NodeID, RoleID) SELECT 1, 1, 1
但不允许
INSERT MyTable (UserID, NodeID, RoleID) SELECT 1, 2, 1
如果第一次插入已经发生,因为这会导致在具有多个节点一个角色的用户。
希望这很简单,我只是把它比它需要在我的脑中更复杂。
答
由于您的约束取决于其他行数据的意见,这排除了过滤的索引。海事组织一个可行的选择可能是一个触发器。这样的触发器可能看起来像这样:
CREATE TRIGGER dbo.MyTrigger ON dbo.Q1
AFTER INSERT, UPDATE
AS
DECLARE @userId INT, @Id INT, @roleId INT, @exists INT;
SELECT TOP 1
@userId = userID
,@roleId = roleID
,@Id = Id
FROM inserted;
SELECT TOP 1
@exists = Id
FROM Q1
WHERE userId = @userId
AND roleID = @roleID AND Id<> @Id;
IF ISNULL(@exists, 0) > 0
BEGIN
-- you would want to either undo the action here when you use an 'after' trigger
-- because as the name implies ... the after means the record is allready inserted/updated
RAISERROR ('No way we would allow this.', 16, 1);
END
-- else
-- begin
-- another alternative would be to use a instead of trigger, which means the record
-- has not been inserted or updated and since that type of trigger runs the trigger 'instead of'
-- updating or inserting the record you would need to do that yourself. Pick your poison ...
-- end
GO
+0
优秀的答案。 – influent
答
的唯一索引应该执行你的要求
CREATE UNIQUE NONCLUSTERED INDEX [idx_Unique] ON [dbo].[MyTable]
(
[UserID] ASC,
[NodeID] ASC,
[RoleID] ASC
)
从我想你将需要两个唯一索引
CREATE UNIQUE NONCLUSTERED INDEX [idx_User_Node] ON [dbo].[MyTable]
(
[UserID] ASC,
[NodeID] ASC
)
GO
CREATE UNIQUE NONCLUSTERED INDEX [idx_User_Role] ON [dbo].[MyTable]
(
[UserID] ASC,
[RoleID] ASC
)
INSERT MyTable(UserID,NodeID,RoleID)SELECT 1,2,2? – bummi
我很好,用户在两个不同的节点有两个不同的角色,这样就可以了。好问题。 :) – influent
那么你不是在寻找唯一的UserID + RoleID? – kgu87