Add Check Constraint not working SQL

Question

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?

@BWS there are about 6000 rows where ab=h (hits head on desk for not seeing the obvious) — thermite
– thermite, Commented Nov 19, 2013 at 19:55
You can add an is_old column to the table and then check ab>h or is_old — Ihor Romanchenko
– Ihor Romanchenko, Commented Nov 19, 2013 at 20:45

Filipe Silva · Accepted Answer · 2013-11-19 19:50:58Z

4

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

answered Nov 19, 2013 at 19:50

Filipe Silva

21.7k6 gold badges56 silver badges70 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

thermite Over a year ago

there are about 6000 rows where ab=h (hits head on desk for not seeing the obvious).

Filipe Silva Over a year ago

Happens to the best :p.

Filipe Silva Over a year ago

@thermite. I didn't realize you edited your question. What do you mena exclude the rows from the constraint? The constraint will be in place for all the rows. Your two options are change your constraint to ab>=h or remove/update the rows that are violating the original cosntraint

thermite Over a year ago

So changing the constraint to ab>=h works for that specific query(actually it is more correct for the problem) but is there a way to add constraints to a table and leave violating data in the table. ie add constraints only to changes you will make to the table.

Filipe Silva Over a year ago

No. If you think about it, that wouldn't make sense. Having a constraint like that ensures that there are no rows here ab < h. If some could be left out of the validation, that statement wouldn't be true.

sapan · Accepted Answer · 2013-11-19 19:59:37Z

3

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);

answered Nov 19, 2013 at 19:59

sapan

2593 gold badges7 silver badges16 bronze badges

Collectives™ on Stack Overflow

Add Check Constraint not working SQL

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related