How should I do this (business logic) in Sql Server? A constraint?

Question

Hi folks,

I wish to add some type of business logic constraint to a table, but not sure how / where.

I have a table with the following fields.

• ID INTEGER IDENTITY
• HubId INTEGER
• CategoryId INTEGER
• IsFeatured BIT
• Foo NVARCHAR(200)

etc.

So what i wish is that you can only have one featured thingy, per hubId + categoryId.

eg.
1, 1, 1, 1, 'blah'  -- Ok.
2, 1, 2, 1, 'more blah' -- Also Ok
3, 1, 1, 1, 'aaa' -- constraint error
4, 1, 1, 0, 'asdasdad' -- Ok.
5, 1, 1, 0, 'bbbb' -- Ok.

etc.

so the third row to be inserted would fail because that hub AND category already have a featured thingy.

Is this possible?

Answer 1

You use a database constraint to protect data. To me, business logic is calculations or complex logic or row-by-row processing: not a flavour of unique constraint

In this case:

1. you can use a trigger to check after the insert
2. define an indexed view filtering IsFeatured = 1 to on HubId, CategoryId, IsFeatured
3. use a filtered index because you have SQL Server 2008 (which is similar to an indexed view)

I'd go for option 3, personally

PS: where is articleID you mentioned?

And this question to: Combining the UNIQUE and CHECK constraints

Edit:

Basically, you'd do

CREATE UNIQUE INDEX IX_Foo ON Mytable (HubId, CategoryId) WHERE IsFeatured = 1

So uniqueness is now checked on (HubId, CategoryId) only for the subset of data where IsFeatured = 1