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:
[SomeType]
IDs (actually, for all but one), UserID
,[IsExpression]
equal to 1IsExpression
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?
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]
.