agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: Scott Rohde <srohde@illinois.edu>
To: pgsql-sql@postgresql.org
Subject: Re: Check/unique constraint question
Date: Tue, 9 Dec 2014 11:01:39 -0700 (MST)
Message-ID: <1418148099196-5829778.post@n5.nabble.com> (raw)
In-Reply-To: <e431ff4c0603050149i45b6469djdaa078343d79b5be@mail.gmail.com>
References: <Pine.LNX.4.64.0603050015050.29717@discord.dyndns.org>
<e431ff4c0603050102m36b68e08w9d84e52e1f8701cb@mail.gmail.com>
<e431ff4c0603050149i45b6469djdaa078343d79b5be@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>
There is something a bit odd about this solution: If you start with an empty
table, the constraint will allow you to do
INSERT INTO foo (active, id) VALUES ('t', 5);
But if you insert this row into the table first and /then/ try to add the
constraint, it will complain that an existing row violates the constraint.
This begs the question of when constraints are checked.
I had always thought of constraints as being static conditions that (unlike
some trigger condition that masquerades as a constraint) apply equally to
existing rows and to rows you are about to add. This seems to show that not
all constraints work this way.
Nikolay Samokhvalov wrote
> just a better way (workaround for subqueries in check constraints...):
>
> CREATE OR REPLACE FUNCTION id_is_valid(
> val INTEGER
> ) RETURNS boolean AS $BODY$
> BEGIN
> IF val IN (
> SELECT id FROM foo WHERE active = TRUE AND id = val
> ) THEN
> RETURN FALSE;
> ELSE
> RETURN TRUE;
> END IF;
> END
> $BODY$ LANGUAGE plpgsql;
> ALTER TABLE foo ADD CONSTRAINT C_foo_iniq_if_true CHECK (active =
> FALSE OR id_is_valid(id));
>
> ...
--
View this message in context: http://postgresql.nabble.com/Check-unique-constraint-question-tp2145289p5829778.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
view thread (11+ messages) latest in thread
Message-ID: <1418148099196-5829778.post@n5.nabble.com>
Permalink: ../1418148099196-5829778.post@n5.nabble.com/
Also on: postgresql.org/message-id/1418148099196-5829778.post@n5.nabble.com
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: srohde@illinois.edu
Subject: Re: Check/unique constraint question
In-Reply-To: <1418148099196-5829778.post@n5.nabble.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox