I am trying to figure out how to write and add a general constraint outside of CREATE TABLE where when the payDate is New Years 2010 (01-01-2010), then the income of different employees must be more than $100,000. Some of the income data on that date is NULL, but I am only trying to find the incomes that are greater than $100k.
The table I am working with:
Employees | Date | Income
John |12-01-2009| 50000
Jake |12-01-2009| 70000
Jill |12-21-2009| 85000.75
Jonn |12-27-2009| 120000.5
June |01-01-2010| 100000.25
Joey |01-01-2010| \N
Jack |01-01-2010| 120000
Jane |01-01-2010| 110000.75
Jean |01-01-2010| \N
Joon |01-01-2010| \N
I have tried:
ALTER TABLE Employees
ADD CONSTRAINT nyIncome
CHECK(payDate = DATE '2010-01-01' AND income > 100000 AND income IS NOT NULL);
This gives me:
"ERROR: check constraint nyIncome is violated by some row"
How would I fix my check so that it would satisfy the conditions?
Edit: The reason why my initial constraint did not work was because there are other dates in the table that were not specified in the condition.
The solution that Jonas provided:
ALTER TABLE Employees ADD CONSTRAINT nyIncome CHECK (payDate != '2010-01-01' OR (payDate = '2010-01-01' AND (income > 100000 OR income IS NULL)));
This solution will go through all the payDates and create a specific condition where income has to be higher than 100000 for the date '2010-01-01' so if for example, a tuple with date '2010-01-01' and income of 80,000 will not meet this constraint and thus cannot be inserted or a income from the date '2010-01-01' cannot be updated with an income less than 100000.
You can't create a constraint on existing tables when some entries don't satisfy the constraint's conditions. In case you really need to check all those conditions, you have to update the already existing entries before creating the constraint to make sure all rows satisfy the conditions. Or if this is not intended, you need to create a before insert trigger instead of a constraint that will check if new entries are allowed or not. If you create such a trigger, you should also check if before insert is sufficient or also before update should be done. In your situation, it seems the conditions of your constraint are incorrect. Likely the correct syntax (according to your description) will be:
ALTER TABLE Employees
ADD CONSTRAINT nyIncome
CHECK (payDate = '2010-01-01' AND (income > 100000 OR income IS NULL));
If you're not sure about the correct conditions for your constraint, execute a select first with the exact conditions as where clause that you want to check by your constraint and see if the result is your entire table. Or revert the where clause and check if the result is empty.
Update because you edited your question: You can't create such a constraint because there are also entries with another date in your table. You likely will need a trigger that forbids to insert/update entries having an income <= 100000 and the date '2010-01-01'. Or just create a precise where clause in your queries instead of a trigger if these restrictions should be applied in queries only.