CODEWITHSUNDEEP
postgresql
kings0712
kings0712

Reputation: 93

postgreSQL check constraint and null

I created a table "TEST" and i tried to input some data however i got an error. The error is ERROR: new row for relation "test" violates check constraint "test_status_check" DETAIL: Failing row contains (5 , 2015-07-21, 15:00:00, I7 , 9 , NULL, NULL). I think it is because of the null of the status. Therefore, i tried put a null in the test table but still not working

Create table test(
    clientID CHAR (20),
    startDate date,
    startTime time,
    instructorNO CHAR(20),
    centreID  CHAR(20), 
    status CHAR(4) CHECK (status IN ('Fail','Pass')) NULL,
    reason VARCHAR(400),

    omitted...
    )

ERROR: new row for relation "test" violates check constraint "test_status_check" DETAIL: Failing row contains (5 , 2015-07-21, 15:00:00, I7 , 9 , NULL, NULL).

Upvotes: 0

Views: 4804

Answers (1)

Brendan Long
Brendan Long

Reputation: 54242

EDIT: I've completely changed my mind. Your existing code is valid (and the NULL part is unnecessary).

According to the documentation,

It should be noted that a check constraint is satisfied if the check expression evaluates to true or the null value. Since most expressions will evaluate to the null value if any operand is null, they will not prevent null values in the constrained columns. To ensure that a column does not contain null values, the not-null constraint described in the next section can be used.

So, something else is messed up. See here for an example of your original code working.

Upvotes: 2

Related Questions