检查两列的约束

发布于 2024-10-10 06:09:14 字数 422 浏览 0 评论 0原文

我想将 Check Constraint 添加到服务器 2005 的表中，但无法解决。

MemberId ClubId MeetingId
1        100    10
2        100    10
3        100    10
7        101    10  <-This would throw a check constraint
1        100    11 
2        100    11

我不想为一个 MeetingId 拥有多个 ClubId 基本上，ClubId 只能属于一个 MeetingId，但可以分配多个成员。

我该如何实现这一目标？

原文

I want to add a Check Constraint to a table for server 2005 but cannot work it out.

MemberId ClubId MeetingId
1        100    10
2        100    10
3        100    10
7        101    10  <-This would throw a check constraint
1        100    11 
2        100    11

I do not want to have more than one ClubId for a single MeetingId
Basically a ClubId can only belong to a single MeetingId but can have more than one member assigned.

How do I achieve this?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

番薯 2024-10-17 06:09:14

在检查约束中使用函数是一种选择，但您可以创建 INDEXED VIEW 完成相同的限制。

CREATE VIEW VIEW_UNIQUE_MEETINGID WITH SCHEMABINDING AS 
  SELECT  ClubID
          , MeetingID
          , COUNT_BIG(*)
  FROM    YourTable
  GROUP BY
          ClubID
          , MeetingID
GO

CREATE UNIQUE CLUSTERED INDEX UIX_VIEW_UNIQUE_MEETINGID ON VIEW_UNIQUE_CLUBID (MeetingID)

Using a function in a Check Constraint is one option but you could create an INDEXED VIEW that accomplishes the same restriction.

CREATE VIEW VIEW_UNIQUE_MEETINGID WITH SCHEMABINDING AS 
  SELECT  ClubID
          , MeetingID
          , COUNT_BIG(*)
  FROM    YourTable
  GROUP BY
          ClubID
          , MeetingID
GO

CREATE UNIQUE CLUSTERED INDEX UIX_VIEW_UNIQUE_MEETINGID ON VIEW_UNIQUE_CLUBID (MeetingID)

回复收藏 0 原文

铁轨上的流浪者 2024-10-17 06:09:14

从msdn推断

编辑：尝试了代码并在@rippos评论后修复了它

CREATE FUNCTION CheckFnctn()
RETURNS int
AS 
BEGIN
   DECLARE @retval int
   SELECT @retval = (select max(cnt) from (select COUNT(distinct ClubID) cnt FROM CheckTbl group by MeetingId) as t )
   RETURN @retval
END;
GO

ALTER TABLE CheckTbl ADD CONSTRAINT chkRowCount CHECK (dbo.CheckFnctn() <= 1 );
GO

inferred from msdn

EDIT: tried the code and fixed it after @rippos comment

CREATE FUNCTION CheckFnctn()
RETURNS int
AS 
BEGIN
   DECLARE @retval int
   SELECT @retval = (select max(cnt) from (select COUNT(distinct ClubID) cnt FROM CheckTbl group by MeetingId) as t )
   RETURN @retval
END;
GO

ALTER TABLE CheckTbl ADD CONSTRAINT chkRowCount CHECK (dbo.CheckFnctn() <= 1 );
GO

回复收藏 0 原文

~没有更多了~