SQL Server - Ensuring uniqueness in one column depending on another column

Question

Sorry if I get the terminology wrong. How to define a constraint in MSSQL Server 2005 to ensure uniqueness in one column depending on another column?

eg. given the last two columns:

1    A    1
2    A    2
3    A    2 <- Disallow because '2' has already appeared alongside 'A'
4    B    1
5    B    2

Answer 1

Try the following:

CREATE TABLE tTable
    (field1 CHAR (1) NOT NULL,
     field2 INT NOT NULL,
     UNIQUE (field1, field2)
)

Answer 2

Create a unique constraint for two columns?

, , , .

Answer 3

It is not necessary to be the primary key, all that needs to be is a unique composite index.