postgreSQL check constraint and null

Question

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

Postgres 9.3 accept your syntax, however, really what is it exactly that you want. Postgres, MySQL, and which version?? — mamdouh alramadan
– mamdouh alramadan, Commented Apr 7, 2015 at 18:03
I removed the MySQL tag since the title clearly says PostgreSQL (and last time I checked, CHECK constraints don't work in MySQL anyway). — Brendan Long
– Brendan Long, Commented Apr 7, 2015 at 18:10

Brendan Long · Accepted Answer · 2015-04-07 19:08:57Z

2

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.

edited Apr 7, 2015 at 19:08

answered Apr 7, 2015 at 18:01

Brendan Long

54.6k21 gold badges154 silver badges194 bronze badges

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

4 Comments

kings0712 Over a year ago

@Bredan Long when i input some data in the table error occur Query failed: ERROR: new row for relation "test" violates check constraint "test_status_check" DETAIL: Failing row contains (2 , 2015-04-29, 10:00:00, I2 , 1 , NULL, NULL).

Brendan Long Over a year ago

@nicholaslui Are you sure you removed the old constraint?

Brendan Long Over a year ago

Hm, weirdly.. your original code works for me on PostgreSQL 9.3.6 :\

kings0712 Over a year ago

hahaha i solve it out lol i put a '' in NULL thats why! thankyou!

Collectives™ on Stack Overflow

postgreSQL check constraint and null

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related