Why is a check constraint not working ?

Started by David Gauthieralmost 6 years ago2 messagesgeneral
Jump to latest
#1David Gauthier
davegauthierpg@gmail.com

psql (9.6.7, server 11.3)

I have a table...

dvdb=# \d+ dvm_events;

Table "dvm.dvm_events"
Column | Type | Modifiers | Storage | Stats
target | Description

------------------+--------------------------+-----------+----------+--------------+----------------------------------------------------------------------
dvm_id | integer | not null | plain |
|
project | character varying | not null | extended |
|
status | character varying | | extended |
|
Check constraints:
"dvm_events_status_check" CHECK (status::text = ANY
(ARRAY['passed'::character varying, 'failed'::character varying,
NULL::character varying]::text[]))

(There's a lot more to the table than what you see here, but I took it out
because it didn't look relevant)

I believe it should disallow an insert with a status of "foo", but...

dvdb=# insert into dvm_events (dvm_id,project,status) values
(99999999,'mero','foo');
INSERT 0 1

This is outside of a transaction and definitely not in a transaction with
deferred constraint checking.

Check constraints seem to work fine if I create a test table having a
constrained column. But this existing table seems to have constraints
disabled for some reason.

What could cause this ?

#2David G. Johnston
david.g.johnston@gmail.com
In reply to: David Gauthier (#1)
Re: Why is a check constraint not working ?

On Wed, Apr 29, 2020 at 8:17 AM David Gauthier <davegauthierpg@gmail.com>
wrote:

Check constraints:
"dvm_events_status_check" CHECK (status::text = ANY
(ARRAY['passed'::character varying, 'failed'::character varying,
NULL::character varying]::text[]))

What could cause this ?

NULL in the array.

"status = NULL" yields NULL which is a pass for a check constraint.

David J.