Unable to apply a constraint when data in a column for multiple rows need to be kept unique for a particular foreign key's reference

Question

Let me explain this scenario, with my table structure-

RoomId  RoomMemberId
R1       RM1
R1       RM2
R1       RM3    
R2       Rm1
R2       RM2
R3       RM1
R3       RM4
R3       RM3

Here in the above table RM1,RM2 and RM3 are the member of R1 room , now I have to apply a constraint that there should not be any other room where only these three are members i.e. there should not be any other room with same room members.

How can i do it at database end, by any unique constraint or any other way to do so ?? Pls help...

@MitchWheat - thnx for your reply. can you pls give some ideas to go with trigger with this table structure. — Rajesh, Jul 25 '16 at 09:32
So when you are inserting into this table,you need to ensure that room members(rm1,rm2,rm3) must not be repeated in other rooms — TheGameiswar, Jul 25 '16 at 09:34
thegameiswar - exactly, that combination of members should be unique — Rajesh, Jul 25 '16 at 09:38
Trigger is one option and also you can create check constraint. Please provide information whether you have existing data in your table or its a new one. — Gaurav Rajput, Jul 26 '16 at 05:30

score 0 · Answer 1 · edited May 23 '17 at 12:32

0

Example of Constraint is as given below :

Sample Table

CREATE TABLE TBLROOMTEST ( ROOMID VARCHAR(100), ROOMMEMBERID VARCHAR(100) )

Create function

CREATE FUNCTION dbo.fn_RoomMemberCheck (@room_id varchar(100), @room_member varchar(100)) RETURNS int AS BEGIN DECLARE @retval int

SELECT @retval = CASE WHEN ROOMID = @room_id THEN 1 ELSE 0 END FROM TBLROOMTEST WHERE ROOMMEMBERID = @room_member

RETURN @retval END;

Add constraint after table creation(but this can be done at the time of table creation itself):

ALTER TABLE TBLROOMTEST ADD CONSTRAINT chk_IfMemberExistsInRoom CHECK (dbo.fn_RoomMemberCheck(ROOMID,ROOMMEMBERID)=0);

Note : It will give you error if you already have data in your table, thats why i asked the same to you. Reference is taken from Here

edited May 23 '17 at 12:32

Community

1
1

answered Jul 26 '16 at 09:00

Gaurav Rajput

617
5
8

thnx gaurav for ur valueable time, here i will insert one row at a time in my table,so ur constraint will fire for my first row's RoomId and RoomMemberId.but i will not have combination of column values (i.e.RM1,RM2,RM3) to check for rows. – Rajesh Jul 26 '16 at 09:50
The problem is that the same combination of members (RM1,RM2 and RM3) should not be alloted another room. but (RM1,RM2 ,RM3, RM4) can get another room , because this a new unique combination, or (RM1,RM2) can also get another room, but only these three i.e. (RM1,RM2 and RM3) should not get any other room because they are already alloted R1 room with the same combination. – Rajesh Jul 26 '16 at 09:50
I understood your above given scenario. Can you give insert logic, how you are inserting data in your table so that i can suggest you accordingly. – Gaurav Rajput Jul 26 '16 at 10:26

score 0 · Answer 2 · answered Aug 21 '19 at 17:48

If the number of room members are limited (say max 5), you could design the table horizontally instead of vertically and artifically make it vertically again in a view.

Room_ID     Room_Label
----------- --------------------------------------------------
3           Aconcagua
1           Kilimanjaro
2           Mount Everest

Member_ID   Member_GivenName                                   Member_LastName
----------- -------------------------------------------------- --------------------------------------------------
1           Alice                                              Smith
2           Bob                                                Taylor
3           Cynthia                                            Miller
4           Dan                                                Cooper

RoomMember_ID Room_ID     Member1_ID  Member2_ID  Member3_ID  Member4_ID  Member5_ID
------------- ----------- ----------- ----------- ----------- ----------- -----------
1             1           1           2           3           NULL        NULL
2             2           1           2           NULL        NULL        NULL
3             3           1           2           3           4           NULL

And the view could look like this:

Room_ID     Member_ID
----------- -----------
1           1
1           2
1           3
2           1
2           2
3           1
3           2
3           3
3           4

Then the RoomMember table needs a unique constraint over the members

ALTER TABLE StackOverflow.RoomMember 
ADD CONSTRAINT UK_RoomMember01 
UNIQUE NONCLUSTERED (
    Member1_ID ASC, Member2_ID ASC, Member3_ID ASC, Member4_ID ASC, Member5_ID ASC
)

and a check constraint that ensures the members can only be entered in ascending order from the left to the right with NULL values to the right:

ALTER TABLE [StackOverflow].[RoomMember] WITH CHECK 
ADD CONSTRAINT [CK_RoomMembers] 
CHECK ((([Member2_ID] IS NULL OR [Member2_ID]>[Member1_ID]) AND ([Member3_ID] IS NULL OR [Member2_ID] IS NOT NULL AND [Member3_ID]>[Member2_ID]) AND ([Member4_ID] IS NULL OR [Member3_ID] IS NOT NULL AND [Member4_ID]>[Member3_ID]) AND ([Member5_ID] IS NULL OR [Member4_ID] IS NOT NULL AND [Member5_ID]>[Member4_ID])))

And that is it. It's now not possible to enter the same combination of members of another room, and the view provides still access in better normalized fashion.

Full source code here:

CREATE TABLE [StackOverflow].[Member](
    [Member_ID] [int] IDENTITY(1,1) NOT NULL,
    [Member_GivenName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [Member_LastName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
 CONSTRAINT [PK_Member] PRIMARY KEY CLUSTERED 
(
    [Member_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [StackOverflow].[Room](
    [Room_ID] [int] IDENTITY(1,1) NOT NULL,
    [Room_Label] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
 CONSTRAINT [PK_Room] PRIMARY KEY CLUSTERED 
(
    [Room_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
 CONSTRAINT [UK_Room] UNIQUE NONCLUSTERED 
(
    [Room_Label] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [StackOverflow].[RoomMember](
    [RoomMember_ID] [int] IDENTITY(1,1) NOT NULL,
    [Room_ID] [int] NOT NULL,
    [Member1_ID] [int] NOT NULL,
    [Member2_ID] [int] NULL,
    [Member3_ID] [int] NULL,
    [Member4_ID] [int] NULL,
    [Member5_ID] [int] NULL,
 CONSTRAINT [PK_RoomMember] PRIMARY KEY CLUSTERED 
(
    [RoomMember_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
 CONSTRAINT [UK_RoomMember01] UNIQUE NONCLUSTERED 
(
    [Member1_ID] ASC,
    [Member2_ID] ASC,
    [Member3_ID] ASC,
    [Member4_ID] ASC,
    [Member5_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [StackOverflow].[Member] ADD  CONSTRAINT [DF_Member_Member_GivenName]  DEFAULT (N'') FOR [Member_GivenName]
GO

ALTER TABLE [StackOverflow].[RoomMember]  WITH CHECK ADD  CONSTRAINT [FK_RoomMember_Member1] FOREIGN KEY([Member1_ID])
REFERENCES [StackOverflow].[Member] ([Member_ID])
GO

ALTER TABLE [StackOverflow].[RoomMember] CHECK CONSTRAINT [FK_RoomMember_Member1]
GO

ALTER TABLE [StackOverflow].[RoomMember]  WITH CHECK ADD  CONSTRAINT [FK_RoomMember_Member2] FOREIGN KEY([Member2_ID])
REFERENCES [StackOverflow].[Member] ([Member_ID])
GO

ALTER TABLE [StackOverflow].[RoomMember] CHECK CONSTRAINT [FK_RoomMember_Member2]
GO

ALTER TABLE [StackOverflow].[RoomMember]  WITH CHECK ADD  CONSTRAINT [FK_RoomMember_Member3] FOREIGN KEY([Member3_ID])
REFERENCES [StackOverflow].[Member] ([Member_ID])
GO

ALTER TABLE [StackOverflow].[RoomMember] CHECK CONSTRAINT [FK_RoomMember_Member3]
GO

ALTER TABLE [StackOverflow].[RoomMember]  WITH CHECK ADD  CONSTRAINT [FK_RoomMember_Member4] FOREIGN KEY([Member4_ID])
REFERENCES [StackOverflow].[Member] ([Member_ID])
GO

ALTER TABLE [StackOverflow].[RoomMember] CHECK CONSTRAINT [FK_RoomMember_Member4]
GO

ALTER TABLE [StackOverflow].[RoomMember]  WITH CHECK ADD  CONSTRAINT [FK_RoomMember_Member5] FOREIGN KEY([Member5_ID])
REFERENCES [StackOverflow].[Member] ([Member_ID])
GO

ALTER TABLE [StackOverflow].[RoomMember] CHECK CONSTRAINT [FK_RoomMember_Member5]
GO

ALTER TABLE [StackOverflow].[RoomMember]  WITH CHECK ADD  CONSTRAINT [FK_RoomMember_Room] FOREIGN KEY([Room_ID])
REFERENCES [StackOverflow].[Room] ([Room_ID])
GO

ALTER TABLE [StackOverflow].[RoomMember] CHECK CONSTRAINT [FK_RoomMember_Room]
GO

ALTER TABLE [StackOverflow].[RoomMember]  WITH CHECK ADD  CONSTRAINT [CK_RoomMembers] CHECK  ((([Member2_ID] IS NULL OR [Member2_ID]>[Member1_ID]) AND ([Member3_ID] IS NULL OR [Member2_ID] IS NOT NULL AND [Member3_ID]>[Member2_ID]) AND ([Member4_ID] IS NULL OR [Member3_ID] IS NOT NULL AND [Member4_ID]>[Member3_ID]) AND ([Member5_ID] IS NULL OR [Member4_ID] IS NOT NULL AND [Member5_ID]>[Member4_ID])))
GO

ALTER TABLE [StackOverflow].[RoomMember] CHECK CONSTRAINT [CK_RoomMembers]
GO

CREATE VIEW [StackOverflow].[V_RoomMember]
AS
SELECT Room_ID, Member1_ID AS Member_ID FROM StackOverflow.RoomMember
UNION
SELECT Room_ID, Member2_ID AS Member_ID FROM StackOverflow.RoomMember WHERE Member2_ID IS NOT NULL
UNION
SELECT Room_ID, Member3_ID AS Member_ID FROM StackOverflow.RoomMember WHERE Member3_ID IS NOT NULL
UNION
SELECT Room_ID, Member4_ID AS Member_ID FROM StackOverflow.RoomMember WHERE Member4_ID IS NOT NULL
UNION
SELECT Room_ID, Member5_ID AS Member_ID FROM StackOverflow.RoomMember WHERE Member5_ID IS NOT NULL
GO


SET IDENTITY_INSERT [StackOverflow].[Room] ON 
GO
INSERT [StackOverflow].[Room] ([Room_ID], [Room_Label]) VALUES (3, N'Aconcagua')
GO
INSERT [StackOverflow].[Room] ([Room_ID], [Room_Label]) VALUES (1, N'Kilimanjaro')
GO
INSERT [StackOverflow].[Room] ([Room_ID], [Room_Label]) VALUES (2, N'Mount Everest')
GO
SET IDENTITY_INSERT [StackOverflow].[Room] OFF
GO
SET IDENTITY_INSERT [StackOverflow].[Member] ON 
GO
INSERT [StackOverflow].[Member] ([Member_ID], [Member_GivenName], [Member_LastName]) VALUES (1, N'Alice', N'Smith')
GO
INSERT [StackOverflow].[Member] ([Member_ID], [Member_GivenName], [Member_LastName]) VALUES (2, N'Bob', N'Taylor')
GO
INSERT [StackOverflow].[Member] ([Member_ID], [Member_GivenName], [Member_LastName]) VALUES (3, N'Cynthia', N'Miller')
GO
INSERT [StackOverflow].[Member] ([Member_ID], [Member_GivenName], [Member_LastName]) VALUES (4, N'Dan', N'Cooper')
GO
SET IDENTITY_INSERT [StackOverflow].[Member] OFF
GO
SET IDENTITY_INSERT [StackOverflow].[RoomMember] ON 
GO
INSERT [StackOverflow].[RoomMember] ([RoomMember_ID], [Room_ID], [Member1_ID], [Member2_ID], [Member3_ID], [Member4_ID], [Member5_ID]) VALUES (1, 1, 1, 2, 3, NULL, NULL)
GO
INSERT [StackOverflow].[RoomMember] ([RoomMember_ID], [Room_ID], [Member1_ID], [Member2_ID], [Member3_ID], [Member4_ID], [Member5_ID]) VALUES (2, 2, 1, 2, NULL, NULL, NULL)
GO
INSERT [StackOverflow].[RoomMember] ([RoomMember_ID], [Room_ID], [Member1_ID], [Member2_ID], [Member3_ID], [Member4_ID], [Member5_ID]) VALUES (3, 3, 1, 2, 3, 4, NULL)
GO
SET IDENTITY_INSERT [StackOverflow].[RoomMember] OFF
GO

Unable to apply a constraint when data in a column for multiple rows need to be kept unique for a particular foreign key's reference

2 Answers2