CODEWITHSUNDEEP
sqlsql-server
Blapple
Blapple

Reputation: 67

SQL Check Constraints apply on all rows

So I've got this table where I keep track of Items, there are 3 kinds of items:

I created 3 check constraints for those:

When I try to add a potion with the following insert;

DECLARE @num int, @Type int, @Name varchar(50), @Description varchar(50), @Gold int

SET @Type = 3
SET @Name = 'Spirit Potion'
SET @Description = 'Restores a bit of Spirit'
SET @Gold = 150

insert into Item(Type, Name, Description, GoldValue) VALUES(@Type, @Name, @Description, @Gold)

I get the following error:

The INSERT statement conflicted with the CHECK constraint "CK_Weapon". The conflict occurred in database "Database", table "dbo.Item".

But it shouldn't trigger this CHECK at all, because Potion Type should be 3!
Is there an easy way for me to alter those CHECKs so it'll only trigger when the Type is the same?

Upvotes: 0

Views: 68

Answers (2)

Erwin Dockx
Erwin Dockx

Reputation: 283

You are trying to put 3 constraints onto the same column, hoping that it will only trigger one of them, matching the Type you are inputting. But it will check them all, that's why the CK_Weapon constraint is violated as it is expecting Type = 1.

You might want to try to write a bit of case-logic inside your constraint, like this:

create table [RPGInventory]
(
      [Type] tinyint not null
    , [PhysDamage] int null
    , [ElemDamage] int null
    , [AttackSpeed] int null
    , [PhysReduction] int null
    , [ElemReduction] int null
    , constraint ckInventoryType check (1 = iif([Type] = (1)
                                                and not ([PhysDamage] + [ElemDamage]) < (1)
                                                and [AttackSpeed] > (0.5), 1
                                          , iif([Type] = (2)
                                                and not ([PhysReduction] + [ElemReduction] < (1)), 1
                                          , iif([Type] = (3)
                                                and ([PhysDamage] + [ElemDamage]) = (0)
                                                and [AttackSpeed] = (0)
                                                and ([PhysReduction] + [ElemReduction]) = (0), 1, 0)))
                                        )
)
go

Upvotes: 0

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239764

You need to reverse the first part of your checks so that they give a "pass" to rows they don't care about. So, e.g. for the Armour check, you should check that either the Type isn't 2 (so this check constraint doesn't care) Or that (the checks that apply to armour) are passed:

CHECK ([Type]!=(2) OR (NOT ([PhysReduction]+[ElemReduction]<(1))))

Repeat for your other checks. At the moment, you cannot insert any rows since the combination of check constraints require that Type be simultaneously equal to 1, 2 and 3.

Upvotes: 3

Related Questions