SQL Server 2005 and heavily edited for security. I have Client and Contact tables and a Link table to give a many-to-many relationship. Each client may have many contacts, but only one main contact, and I am having problems trying to enforce that constraint
by using a CHECK constraint calling a function to detect multiple main contacts.
CREATE TABLE [dbo].[Link]([Client] int NOT NULL, [Contact] int NOT NULL, [IsMain] bit NOT NULL)
The primary key is [Client] + [Contact].
CREATE FUNCTION [dbo].[LinkCheckFunction] (@Client int) RETURNS bit AS
RETURN CASE WHEN ((SELECT COUNT([IsMain]) FROM [dbo].[Link]
WHERE (([Client] = @Client) AND ([IsMain] = 1))) > 1) THEN 1 ELSE 0 END;
ALTER TABLE [dbo].[Link] ADD CONSTRAINT [CK_Link] CHECK ([dbo].[LinkCheckFunction] ([Client]) = 0)
This does not prevent updating of existing rows or insertion of new rows which would result in a client having multiple main contacts.
However, writing the function without a parameter and checking the entire table for cases of a client with multiple main contacts does work.
There was a post 'Check constraint across multiple records?' by Mark SQL Server on March 12, 2009 on this issue. As far as I can
see, my approach matches some of the replies to that post, but does not work.
Any ideas? Please note that the post is heavily edited fo
View Complete Post