CODEWITHSUNDEEP
sqlsql-server
Hanna
Hanna

Reputation: 10753

Multiple Column Constraint Issue

I'm trying to write a constraint that says that per ID, there can only be one row with value X.

That might not make much sense so let me give you an example:

Let's say that I have 3 rows and two columns in a table, each row has the same ID (column 1), but the second column has varying values, two of which are identical and the third is unique. How do I ensure that the unique value stays unique?

Here is the constraint I've been using:

ADD CONSTRAINT myConstraint UNIQUE (col1, col2);

I thought that I can make col2 NULL and have duplicates, and then have a NOT NULL when I was it to be unique (as I only care about a single value being unique). The reason this doesn't work is that for some reason my constraint is not allowing duplicate NULLS, almost like it knows how to compare NULLs...

Ideally I'd like to say

ADD CONSTRAINT myConstraint UNIQUE (col1, col2 = 'REQUESTED');

but something like that doesn't exist.

I've looked into CHECK constraints but I'm not sure how to tell it to do this using a check constraint.

Basically I want to take the incoming ID and make sure that there's no other identical ID that has a column2 value of REQUESTED.

Sorry if this doesn't make a lot of sense, I'm doing my best to explain this.

Upvotes: 0

Views: 126

Answers (1)

Mihail Golubev
Mihail Golubev

Reputation: 126

You can use filtered indexes: http://msdn.microsoft.com/en-us/library/cc280372(v=sql.100).aspx

CREATE UNIQUE NONCLUSTERED INDEX [test_idx] ON [dbo].[test] ([col1], [col2])
WHERE ([col2] IS NOT NULL)

It's unique index, so it will not allow you to insert duplicate col2 values for a given ID(col1) and serve as the constraint you want. In your case, you can also use col2 = 'REQUESTED' filter.

Upvotes: 1

Related Questions