SQL Constraint on column value depending on value of other column

Question

First, I have simple [SomeType] table, with columns [ID] and [Name].

Also I have [SomeTable] table, with fields like:

[ID], 
[SomeTypeID] (FK), 
[UserID] (FK), 
[IsExpression]

Finally, I have to made on database layer a constraint that:

• for concrete [SomeType] IDs (actually, for all but one),
• for same UserID,
• only one entry should have [IsExpression] equal to 1
  (IsExpression is of BIT type)

I don't know if it's complex condition or not, but I have no idea how to write it. How would you implement such constraint?

Answer 1

You can do this with filtered index:

CREATE UNIQUE NONCLUSTERED INDEX [IDX_SomeTable] ON [dbo].[SomeTable]
(
    [UserID] ASC
)
WHERE ([SomeTypeID] <> 1 AND [IsExpression] = 1)

or:

CREATE UNIQUE NONCLUSTERED INDEX [IDX_SomeTable] ON [dbo].[SomeTable]
(
    [UserID] ASC,
    [SomeTypeID] ASC
)
WHERE ([SomeTypeID] <> 1 AND [IsExpression] = 1)

Depends on what you are trying to achieve. Only one [IsExpression] = 1 within one user without consideration of [SomeTypeID] or you want only one [IsExpression] = 1 within one user and one [SomeTypeID].