Check constraint over two columns

Question

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?

Is this table denormalized for performance reasons? Is `MeetingID` an FK? If so, is there a `ClubId` column in the meeting table? — outis, Jan 04 '11 at 11:46
If that's so, isn't ClubId an attribute of a particular meeting, rather than an attribute of this (Attendees?) table? — Damien_The_Unbeliever, Jan 04 '11 at 11:46
Reason for club Id is because this is a old legacy system where the creator decided to create a PK based on those three fields! Ugly but it is the way it is — Rippo, Jan 04 '11 at 11:59
A slow but sure process! I am on my way but just need to add some constraints to make sure data is cleansed before I do so! — Rippo, Jan 04 '11 at 12:36

score 4 · Accepted Answer · answered Jan 04 '11 at 13:42

4

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)

answered Jan 04 '11 at 13:42

Lieven Keersmaekers

57,207
13
112
146

Got error `Cannot schema bind view 'VIEW_UNIQUE_MEETINGID' because name 'MemberMeetings' is invalid for schema binding.` – Rippo Jan 04 '11 at 15:23
1

@Rippo - you need to use a two-part name to refer to your table, e.g. `dbo.MemberMeetings` if it's in the `dbo` schema. – Damien_The_Unbeliever Jan 04 '11 at 15:33
Duh! thanks @Lieven, this works really nicely. Now to work out why it works :) – Rippo Jan 04 '11 at 16:57
@Rippo, the indexed view is essentially a table that's a subset of your original table. The subset only contains the ClubID & MeetingID (COUNT_BIG is only necessary to be able to use a GROUP BY). On this subset table, there's a unique constraint on MeetingID. – Lieven Keersmaekers Jan 04 '11 at 17:26

bernd_k · Answer 2 · 2011-01-04T15:46:47.477

2

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

edited Jan 04 '11 at 15:46

answered Jan 04 '11 at 11:48

bernd_k

11,558
7
45
64

+1 but if memory serves me well, this will fail if you `UPDATE` ClubId or MeetingID. To resolve this, you should pass both ClubID and MeetingID to the function. – Lieven Keersmaekers Jan 04 '11 at 13:37
This can also fail under snapshot isolation I think. Look for articles by Hugo Kornelis or Alex Kuznetsov on this. – Martin Smith Jan 04 '11 at 13:50
@Lieven and @Martin Thanks for the comments. I only did ad hoc Google search. I never used check constraints in praxis. – bernd_k Jan 04 '11 at 14:19
Can't seem to get this to work as intended. Did you try it out yourself first? – Rippo Jan 04 '11 at 15:18
@Rippo. Oops tried it and fixed it. I guess @Lieven and @martins comments still apply. – bernd_k Jan 04 '11 at 15:48
+1 anyway, :P thanks, have decided to go with @Lieven as it works really nicely – Rippo Jan 04 '11 at 16:56

Check constraint over two columns

2 Answers2