Re: Question about check constraints - Mailing list pgsql-sql

Both concerns.
1) There are actually more than two columns with such checks, and each
one calls a few functions which execute some more queries. So I would
like to invoke these checks only when necessary.
2) The bigger concern is the side effect: Here's my schema:
CREATE TABLE vm_device
(      device_id      INTEGER      UNIQUE NOT NULL                               REFERENCES device_table(device_id)
                         ON UPDATE CASCADE                               ON DELETE CASCADE,   preference     VARCHAR(1)
 NOT NULL DEFAULT 'U'                               CHECK (CASE WHEN preference = 'U'
       THEN true                                           ELSE validate_preference()
  END),   enabled        BOOLEAN      NOT NULL DEFAULT false                               CHECK (CASE WHEN enabled =
false                                          THEN true                                           ELSE 
validate_system_enabled() AND
validate_enabled(device_id)                                      END),   attach_vm      BOOLEAN      NOT NULL DEFAULT
false                              CHECK (CASE WHEN attach_vm = false                                           THEN
true                                          ELSE validate_attach_vm()                                      END), 
   PRIMARY KEY (device_id)
) WITHOUT OIDS;

This table contains some information about a device in my system.
The issue is with the enabled column. It basically enables/disables the
device.
The device can be enabled only when the two check conditions pass. But
once it is
enabled, the conditions of the system might change such that if executed
again, these
conditions might not pass. We want to allow such situations. The problem
arises when
we want to change the value of some other column, say attach_vm.
Although the check
constraints for the attach_vm column pass, those for enabled column
fail, and I cannot
complete my updates.

Any suggestions on the best way to overcome this?

Thanks,
kashmira

-----Original Message-----
From: Michael Fuhr [mailto:mike@fuhr.org]
Sent: Friday, January 27, 2006 4:40 PM
To: Kashmira Patel (kupatel)
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] Question about check constraints

On Fri, Jan 27, 2006 at 03:06:26PM -0800, Kashmira Patel (kupatel)
wrote:
>   I have a table where two columns have two different check
> constraints associated with them. When I update one column, the check
> constraint on the other column is also executed. Is there a way to
> avoid this? I want to check only for the condition defined for the
column being updated.

I don't think you can change this behavior: each CHECK constraint is
evaluated for the new row regardless of whether a particular column
changed or not.  However, you could enforce the constraints with a
trigger and skip checks where NEW.column is the same as OLD.column.

Why the concern?  Are the checks expensive?  Do they have side effects?
What do they do?

--
Michael Fuhr