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

Question

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

E.g. considering 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 this:

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

Answer 2

Create a unique constraint on the 2 columns ?

This is the most logical thing to do, since it seems that this one column is not unique, but the combination of the 2 columns must be unique.

Answer 3

Doesn't need to be a primary key, all it needs to be is a unique composite index.