SQl Constraint UNIQUE based on other column value

Question

Table A has columns 1 and 2. Column 1's value must be unique if column 2 is equal to x.

ALTER TABLE A
ADD UNIQUE (1) WHERE 2 = x.

But this gives me a syntax error near WHERE.

I tried to create an index, but I can't figure out how to make that do what I want either.

score 4 · Accepted Answer · answered Feb 25 '13 at 19:57

4

Create unique nonclustered index [my_index]
on [TableA]([1])
where [2] = x

answered Feb 25 '13 at 19:57

jfin3204

699
6
18

1

+1 -- much better solution -- just add single quotes around the x I think. – sgeddes Feb 25 '13 at 20:16
oops. I believe you are right about the single quotes. Good catch – jfin3204 Feb 25 '13 at 20:18
Thanks! I was trying to create an index using the designer and couldn't figure out how. This is much easier! – Shawn Feb 25 '13 at 20:23
If the reason for the index is optimizing a query, it will not work, see http://dba.stackexchange.com/questions/117665/why-select-count-query-execution-plan-includes-left-joined-table/117671?noredirect=1#comment214471_117671 which links https://connect.microsoft.com/SQLServer/feedback/details/782213/recognize-unique-filtered-indexes-as-unique – Tomas Kubes Oct 12 '15 at 18:03

score 0 · Answer 2 · answered Feb 25 '13 at 20:13

Here's an alternative solution using a Function although the index is a better solution:

CREATE FUNCTION dbo.CheckConstraint
(
   @col1 int,
   @col2 CHAR(1)
)
RETURNS INT
AS
BEGIN

  DECLARE @ret INT
  SELECT @ret = COUNT(*) 
  FROM YourTable 
  WHERE col1 = @col1 AND @col2 = 'X' 
  RETURN @ret

END;

CREATE TABLE YourTable (col1 int, col2 char(1));

ALTER TABLE YourTable
  ADD CONSTRAINT CheckForXConstraint CHECK (NOT (dbo.CheckConstraint(col1,col2) > 1));

INSERT INTO YourTable VALUES (1, 'X');
INSERT INTO YourTable VALUES (2, 'X');
INSERT INTO YourTable VALUES (2, 'Y');
INSERT INTO YourTable VALUES (2, 'X');   <-- This line fails

SQL Fiddle Demo

SQl Constraint UNIQUE based on other column value

2 Answers2