How to create a check condition that checks for if date is a specific date then income has to be &gt-CodePudding

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.

CodePudding user response：

The exact error message you are seeing would be shown if there were one or more already existing records in your table which failed the check constraint. To find such records, you may use the following query:

SELECT *
FROM Employees
WHERE payDate <> '2010-01-01' OR income > 100000 OR income IS NOT NULL;

Actually, your check constraint is very specific, and it would seem to me that most data would fail to match it. Perhaps you intended for the negative of this check constraint:

ALTER TABLE Employees
ADD CONSTRAINT nyIncome
CHECK (payDate <> '2010-01-01' OR income > 100000 OR income IS NOT NULL);

CodePudding user response：

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.

CodePudding user response：

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,

Other people already pointed out that you cannot use check constraint for (paydate = 2010-01-01 then income is not null) because some rows violate the condition.
So the following answer is for set constraint for: if paydate = 2010-01-01 then income = null or income > 100000.

via check constraint. Meaning check the current rows and incoming rows. if not meet the criteria then error will be invoke.

ALTER TABLE emp
    ADD CONSTRAINT nyIncome CHECK (
        (payDate = DATE '2010-01-01' AND income > 100000)
    OR (payDate = DATE '2010-01-01' AND income IS NULL)
    OR (payDate <> DATE '2010-01-01'));
drop trigger trg_special_date on emp;

via trigger. Obviously trigger does not validate the existing row.

CREATE OR REPLACE FUNCTION trg_special_date ()
    RETURNS TRIGGER
    AS $$
BEGIN
    IF NEW.paydate = '2010-01-01' THEN
        IF NEW.income IS NULL THEN
            RETURN new;
        elsif NEW.income > 100000 THEN
            RETURN new;
        ELSE
            RETURN NULL;
        END IF;
    ELSE
        RETURN new;
    END IF;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER trg_special_date
    BEFORE INSERT OR UPDATE ON emp FOR EACH ROW
    EXECUTE PROCEDURE trg_special_date ();

note: this trigger will simple ignore the row that not meet the criteria, You can customize it, so if the incoming row not meet the criteria then invoke an error.

another way is row level security, kind of hard. another way is view with security_barrier or security_invoker or with check_option. (will do it in the future).