title: Optional Unique Constraints?
body: <p>I am setting up a <a href="http://en.wikipedia.org/wiki/Software_as_a_Service" rel="nofollow">SaaS</a> 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.</p>
  
  <p>Has anyone tackled a problem like this, and if so, what are the common solutions and or potential pitfalls to look out for?</p>
  
  <p>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.</p>
  
  <p><strong>SOLUTION:</strong><br>
  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.</p>
  
  <p>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.</p>
  
  <p>Here is the test tables, data, and function I used to 
  verify that a check constraint could do all that I wanted.</p>
  
  <pre><code>-- 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</code></pre>
  
  <p><strong>EDIT:</strong><br>
  Had to modify the function a few times to catch NULL values in the dupe check, but all appears to be working now.</p>