CODEWITHSUNDEEP
sqlpostgresqlcheck-constraints
thermite
thermite

Reputation: 512

Add Check Constraint not working SQL

I need to add a constraint to a table such that column ab is always greater than column h. I have tried

ALTER TABLE batting
ADD constraint possibleHits check (ab>h);

But that returns 

ERROR:  check constraint "possiblehits" is violated by some row

********** Error **********

ERROR: check constraint "possiblehits" is violated by some row
SQL state: 23514

as an error.

I have run 

select * from batting where ab<h

and it returns no rows.

Any ideas on what i'm doing wrong? or is there a way to do something like add 

NOT VALID

to the statement so that it will not enforce the constraint on existing rows? I know that that works in mysql but in postgres it only works on keys.

EDIT:

So as was pointed out there are instances where ab = h however is there a way to exclude existing rows from the constraint?

Upvotes: 2

Views: 8318

Answers (2)

sapan
sapan

Reputation: 259

If you want column ab is always greater than column h, then you need to delete those 6000 rows where ab=h, if don't want to delete those 6000 rows, you can try this.

 ALTER TABLE batting
 ADD constraint possibleHits check(ab>=h);

Upvotes: 3

Filipe Silva
Filipe Silva

Reputation: 21677

You should check for values where ab=h. I'm sure you'll find your problem.

See below for one example of it working.

sqlfiddle demo

Upvotes: 4

Related Questions