From: Tom Lane <tgl@sss.pgh.pa.us>
To: Bruno Wolff III <bruno@wolff.to>
Cc: Jeff Frost <jeff@frostconsultingllc.com>
Cc: Phillip Tornroth <ptornroth@intellidot.net>
Cc: pgsql-sql@postgresql.org
Subject: Re: Multi-Column Constraints and Null Values
Date: Sat, 29 Apr 2006 18:02:41 -0400
Message-ID: <9554.1146348161@sss.pgh.pa.us> (raw)
In-Reply-To: <20060429202737.GB14915@wolff.to>
References: <303D0B49-F8EC-491C-8865-B7B819120C13@intellidot.net>
<Pine.LNX.4.64.0604291314040.9581@discord.dyndns.org>
<20060429202737.GB14915@wolff.to>
Bruno Wolff III <bruno@wolff.to> writes:
> To block multiple entries where one of the columns is null he needs to
> add extra checks. One way to do this is to add a partial index for each column
> with the condition that the other column IS NULL. This will cover everything
> but the case where both columns are null. If this case isn't allowed, then
> a row constraint can be used to block it. If it is allowed, the only thing that
> comes to mind is making an SQL function that counts the number of rows where
> both columns are null and calling that function in a row constraint and
> checking that the result is <= 1.
Nah, it's easy:
CREATE UNIQUE INDEX fooi ON foo ((0))
WHERE col1 IS NULL AND col2 IS NULL;
So you'd need a total of four unique indexes (3 of 'em partial) to
enforce the condition for two columns. The real problem with this
is that it doesn't scale well for more than two columns :-(
regards, tom lane
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: tgl@sss.pgh.pa.us, bruno@wolff.to, jeff@frostconsultingllc.com, ptornroth@intellidot.net
Subject: Re: Multi-Column Constraints and Null Values
In-Reply-To: <9554.1146348161@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox