CODEWITHSUNDEEP
t-sqldatabase-designsql-server-2008constraintsunique
Pure.Krome
Pure.Krome

Reputation: 86937

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

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.

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?

Upvotes: 3

Views: 330

Answers (1)

gbn
gbn

Reputation: 432200

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

Upvotes: 3

Related Questions