Optional Unique Constraints?

Question

I am setting up a SaaS application that multiple clients will use to enter data. However, I have certain fields that Client A may want to force unique, Client B may want to allow dupes. Obviously if I am going to allow any one client to have dupes, the table may not have a unique constraint on it. The downside is that If I want to enforce a unique constraint for some clients, I will have to go about it in some other way.

Has anyone tackled a problem like this, and if so, what are the common solutions and or potential pitfalls to look out for?

I am thinking a trigger that checks for any possible unique flags may be the only way to enforce this correctly. If I rely on the business layer, there is no guarentee that the app will do a unique check before every insert.

SOLUTION:
First I considered the Unique Index, but ruled it out as they can not do any sort of joins or lookups, only express values. And I didn't want to modify the index every time a client was added or a client's uniqueness preference changed.

Then I looked into CHECK CONSTRAINTS, and after some fooling around, built one function to return true for both hypothetical columns that a client would be able to select as unique or not.

Here is the test tables, data, and function I used to verify that a check constraint could do all that I wanted.

-- Clients Table
CREATE TABLE [dbo].[Clients](
    [ID] [int]  NOT NULL,
    [Name] [varchar](50) NOT NULL,
    [UniqueSSN] [bit] NOT NULL,
    [UniqueVIN] [bit] NOT NULL
) ON [PRIMARY]

-- Test Client Data
INSERT INTO Clients(ID, Name, UniqueSSN, UniqueVIN) VALUES(1,'A Corp',0,0)
INSERT INTO Clients(ID, Name, UniqueSSN, UniqueVIN) VALUES(2,'B Corp',1,0)
INSERT INTO Clients(ID, Name, UniqueSSN, UniqueVIN) VALUES(3,'C Corp',0,1)
INSERT INTO Clients(ID, Name, UniqueSSN, UniqueVIN) VALUES(4,'D Corp',1,1)

-- Cases Table
CREATE TABLE [dbo].[Cases](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [ClientID] [int] NOT NULL,
    [ClaimantName] [varchar](50) NOT NULL,
    [SSN] [varchar](12) NULL,
    [VIN] [varchar](17) NULL
) ON [PRIMARY]

-- Check Uniques Function
CREATE FUNCTION CheckUniques(@ClientID int)
RETURNS int -- 0: Ok to insert, 1: Cannot insert
AS
BEGIN
    DECLARE @SSNCheck int
    DECLARE @VinCheck int
    SELECT @SSNCheck = 0
    SELECT @VinCheck = 0
    IF (SELECT UniqueSSN FROM Clients WHERE ID = @ClientID) = 1
    BEGIN
        SELECT @SSNCheck = COUNT(SSN) FROM Cases cs WHERE ClientID = @ClientID AND (SELECT COUNT(SSN) FROM Cases c2 WHERE c2.SSN = cs.SSN) > 1
    END
    IF (SELECT UniqueVIN FROM Clients WHERE ID = @ClientID) = 1
    BEGIN
        SELECT @VinCheck = COUNT(VIN) FROM Cases cs WHERE ClientID = @ClientID AND (SELECT COUNT(VIN) FROM Cases c2 WHERE c2.VIN = cs.VIN) > 1
    END
    RETURN @SSNCheck + @VinCheck
END

-- Add Check Constraint to table
ALTER TABLE Cases
ADD Constraint chkClientUniques CHECK(dbo.CheckUniques(ClientID) = 0)

-- Now confirm constraint using test data

-- Client A: Confirm that both duplicate SSN and VIN's are allowed
INSERT INTO Cases (ClientID, ClaimantName, SSN, VIN) VALUES(1, 'Alice', '111-11-1111', 'A-1234')
INSERT INTO Cases (ClientID, ClaimantName, SSN, VIN) VALUES(1, 'Bob', '111-11-1111', 'A-1234')

-- Client B: Confirm that Unique SSN is enforced, but duplicate VIN allowed
INSERT INTO Cases (ClientID, ClaimantName, SSN, VIN) VALUES(2, 'Charlie', '222-22-2222', 'B-2345') -- Should work
INSERT INTO Cases (ClientID, ClaimantName, SSN, VIN) VALUES(2, 'Donna', '222-22-2222', 'B-2345') -- Should fail
INSERT INTO Cases (ClientID, ClaimantName, SSN, VIN) VALUES(2, 'Evan', '333-33-3333', 'B-2345') -- Should Work

-- Client C: Confirm that Unique VIN is enforced, but duplicate SSN allowed
INSERT INTO Cases (ClientID, ClaimantName, SSN, VIN) VALUES(3, 'Evan', '444-44-4444', 'C-3456') -- Should work
INSERT INTO Cases (ClientID, ClaimantName, SSN, VIN) VALUES(3, 'Fred', '444-44-4444', 'C-3456') -- Should fail
INSERT INTO Cases (ClientID, ClaimantName, SSN, VIN) VALUES(3, 'Ginny', '444-44-4444', 'C-4567') -- Should work

-- Client D: Confirm that both Unique SSN and VIN are enforced
INSERT INTO Cases (ClientID, ClaimantName, SSN, VIN) VALUES(4, 'Henry', '555-55-5555', 'D-1234') -- Should work
INSERT INTO Cases (ClientID, ClaimantName, SSN, VIN) VALUES(4, 'Isaac', '666-66-6666', 'D-1234') -- Should fail
INSERT INTO Cases (ClientID, ClaimantName, SSN, VIN) VALUES(4, 'James', '555-55-5555', 'D-2345') -- Should fail
INSERT INTO Cases (ClientID, ClaimantName, SSN, VIN) VALUES(4, 'Kevin', '555-55-5555', 'D-1234') -- Should fail
INSERT INTO Cases (ClientID, ClaimantName, SSN, VIN) VALUES(4, 'Lisa', '777-77-7777', 'D-3456') -- Should work

EDIT:
Had to modify the function a few times to catch NULL values in the dupe check, but all appears to be working now.

Answer 1

One approach is to use a CHECK constraint instead of a unique. This CHECK constraint will be backed by a SCALAR function that will

1. take as input ClientID
2. cross-ref ClientID against a lookup table to see if duplicates are allowed (client.dups)
3. if not allowed, check for duplicates in the table

Something like

ALTER TABLE TBL ADD CONSTRAINT CK_TBL_UNIQ CHECK(dbo.IsThisOK(clientID)=1)