0

I need some help with adding a check in one of the added columns to the existing tables. I am not sure if that will be a computed column or check constraint.

Table changes to Products table, new column is IsDefault and existing column name is banId. IsDefault value is 0 but when turned to 1 is only possible for one per banId column.

IsDefault BIT (only one per banId, default 0)

I need to add it to this code to add that one per banId condition

ALTER TABLE [dbo].products
    ADD IsDefault BIT NOT NULL
    CONSTRAINT DF_products_IsDefault DEFAULT 0 

ProductId   banId           IsDefault
-------------------------------------
1             1                   0
2             1                   1
3             1                   0
4             2                   0
5             2                   0
6             2                   0
7             3                   1
8             4                   1
9             4                   0
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Sarah
  • 1,199
  • 2
  • 21
  • 42
  • 1
    use a unique filtered index. – Martin Smith Nov 14 '18 at 20:16
  • 1
    [this answer](https://stackoverflow.com/a/15776793/6167855) on that duplicate question – S3S Nov 14 '18 at 20:18
  • I saw that post @MartinSmith, it recommends placing a filtered Index, I placed that and it does that work but this sounds more like a constraint or a check in a table. My understanding of indexes is that they are used for improving table scan's and search results not for checking values added to the table. Not sure if thats solution makes complete sense. – Sarah Nov 14 '18 at 20:53
  • 2
    SQL has a concept of unique constraints. These are similar in concept to other types of constraints. In SQL Server if you create a unique constraint it also creates a unique index so it can enforce it efficiently. Here you are just creating the index yourself to enforce your desired rule. – Martin Smith Nov 14 '18 at 20:58
  • @MartinSmith, thank you so much, now it makes more sense. Learned something new on this today. – Sarah Nov 15 '18 at 21:54

0 Answers0