CODEWITHSUNDEEP
sqlsql-server-2008constraintswhere-clauseunique-constraint
Shawn
Shawn

Reputation: 2366

SQl Constraint UNIQUE based on other column value

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.

Upvotes: 0

Views: 1704

Answers (2)

sgeddes
sgeddes

Reputation: 62831

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

Upvotes: 0

jfin3204
jfin3204

Reputation: 709

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

Upvotes: 4

Related Questions