0

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?

prvit
  • 956
  • 3
  • 9
  • 22

1 Answers1

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].

Giorgi Nakeuri
  • 35,155
  • 8
  • 47
  • 75
  • will index help me to avoid adding new entry with also [IsExpression] true, if this user already has IsExpression true for this SomeType ? – prvit Jun 12 '15 at 09:40
  • @prvit, the second index is exactly what you want – Giorgi Nakeuri Jun 12 '15 at 09:45
  • @prvit yes, if a index is created as `UNIQUE` then this means you cannot enter any duplicate records into the fields which are part of the index. – SQL Police Jun 12 '15 at 10:44