pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedMulti-Column Constraints and Null Values
5+ messages / 5 participants
[nested] [flat]
* Multi-Column Constraints and Null Values
@ 2006-04-27 21:06 Phillip Tornroth <ptornroth@intellidot.net>
2006-04-29 20:14 ` Re: Multi-Column Constraints and Null Values Jeff Frost <jeff@frostconsultingllc.com>
0 siblings, 1 reply; 5+ messages in thread
From: Phillip Tornroth @ 2006-04-27 21:06 UTC (permalink / raw)
To: pgsql-sql
I have a problem in a number of my tables. I'd like to add uniqueness
constraints across multiple columns, but some of the columns are
nullable. This gives me trouble since when I create a constraint on
columns A and B.. I'd like the constraint to be enforced such that
you couldn't insert values ("value for A", null) twice. I understand
why the constraints work this way, but I'm wondering if anyone knows
of a workaround.
Feel free to spare me any "don't use nullable columns" responses. I
of course am aware that is an option. It's just one I'm hoping to
avoid as this is a legacy database and it would be a mammoth effort
to accomplish.
Thank you in advance!
Phill
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Multi-Column Constraints and Null Values
2006-04-27 21:06 Multi-Column Constraints and Null Values Phillip Tornroth <ptornroth@intellidot.net>
@ 2006-04-29 20:14 ` Jeff Frost <jeff@frostconsultingllc.com>
2006-04-29 20:27 ` Re: Multi-Column Constraints and Null Values Bruno Wolff III <bruno@wolff.to>
0 siblings, 1 reply; 5+ messages in thread
From: Jeff Frost @ 2006-04-29 20:14 UTC (permalink / raw)
To: Phillip Tornroth <ptornroth@intellidot.net>; +Cc: pgsql-sql
On Thu, 27 Apr 2006, Phillip Tornroth wrote:
> I have a problem in a number of my tables. I'd like to add uniqueness
> constraints across multiple columns, but some of the columns are nullable.
> This gives me trouble since when I create a constraint on columns A and B..
> I'd like the constraint to be enforced such that you couldn't insert values
> ("value for A", null) twice. I understand why the constraints work this way,
> but I'm wondering if anyone knows of a workaround.
>
Phill,
You likely want a multicolumn unique index created like so:
CREATE UNIQUE INDEX foo_A_B_unique_idx ON foo (A,B);
See the docs here:
http://www.postgresql.org/docs/current/interactive/indexes-unique.html
--
Jeff Frost, Owner <jeff@frostconsultingllc.com>
Frost Consulting, LLC http://www.frostconsultingllc.com/
Phone: 650-780-7908 FAX: 650-649-1954
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Multi-Column Constraints and Null Values
2006-04-27 21:06 Multi-Column Constraints and Null Values Phillip Tornroth <ptornroth@intellidot.net>
2006-04-29 20:14 ` Re: Multi-Column Constraints and Null Values Jeff Frost <jeff@frostconsultingllc.com>
@ 2006-04-29 20:27 ` Bruno Wolff III <bruno@wolff.to>
2006-04-29 22:02 ` Re: Multi-Column Constraints and Null Values Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 5+ messages in thread
From: Bruno Wolff III @ 2006-04-29 20:27 UTC (permalink / raw)
To: Jeff Frost <jeff@frostconsultingllc.com>; +Cc: Phillip Tornroth <ptornroth@intellidot.net>; pgsql-sql
On Sat, Apr 29, 2006 at 13:14:36 -0700,
Jeff Frost <jeff@frostconsultingllc.com> wrote:
> On Thu, 27 Apr 2006, Phillip Tornroth wrote:
>
> >I have a problem in a number of my tables. I'd like to add uniqueness
> >constraints across multiple columns, but some of the columns are nullable.
> >This gives me trouble since when I create a constraint on columns A and
> >B.. I'd like the constraint to be enforced such that you couldn't insert
> >values ("value for A", null) twice. I understand why the constraints work
> >this way, but I'm wondering if anyone knows of a workaround.
> >
>
> Phill,
>
> You likely want a multicolumn unique index created like so:
>
> CREATE UNIQUE INDEX foo_A_B_unique_idx ON foo (A,B);
>
> See the docs here:
> http://www.postgresql.org/docs/current/interactive/indexes-unique.html
Creating a multicolumn key is a simpler way of doing the same thing.
However, either way of doing the above doesn't completely solve his problem.
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Multi-Column Constraints and Null Values
2006-04-27 21:06 Multi-Column Constraints and Null Values Phillip Tornroth <ptornroth@intellidot.net>
2006-04-29 20:14 ` Re: Multi-Column Constraints and Null Values Jeff Frost <jeff@frostconsultingllc.com>
2006-04-29 20:27 ` Re: Multi-Column Constraints and Null Values Bruno Wolff III <bruno@wolff.to>
@ 2006-04-29 22:02 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-04-30 02:48 ` Re: Multi-Column Constraints and Null Values Tornroth, Phill <ptornroth@intellidot.net>
0 siblings, 1 reply; 5+ messages in thread
From: Tom Lane @ 2006-04-29 22:02 UTC (permalink / raw)
To: Bruno Wolff III <bruno@wolff.to>; +Cc: Jeff Frost <jeff@frostconsultingllc.com>; Phillip Tornroth <ptornroth@intellidot.net>; pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Multi-Column Constraints and Null Values
2006-04-27 21:06 Multi-Column Constraints and Null Values Phillip Tornroth <ptornroth@intellidot.net>
2006-04-29 20:14 ` Re: Multi-Column Constraints and Null Values Jeff Frost <jeff@frostconsultingllc.com>
2006-04-29 20:27 ` Re: Multi-Column Constraints and Null Values Bruno Wolff III <bruno@wolff.to>
2006-04-29 22:02 ` Re: Multi-Column Constraints and Null Values Tom Lane <tgl@sss.pgh.pa.us>
@ 2006-04-30 02:48 ` Tornroth, Phill <ptornroth@intellidot.net>
0 siblings, 0 replies; 5+ messages in thread
From: Tornroth, Phill @ 2006-04-30 02:48 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; Bruno Wolff III <bruno@wolff.to>; +Cc: Jeff Frost <jeff@frostconsultingllc.com>; pgsql-sql
Wow. This is great feedback. As for more than two columns.. I don't think I have more than two nullable columns. Often I have a three or four column constraint where only one or two of them are nullable. I've never used constraints or indexes with where clauses. I'll have to play with this a bit to understand how it works. Thanks for the tips!
Phill
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Sat 4/29/2006 3:02 PM
To: Bruno Wolff III
Cc: Jeff Frost; Tornroth, Phill; pgsql-sql@postgresql.org
Subject: Re: [SQL] Multi-Column Constraints and Null Values
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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2006-04-30 02:48 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-04-27 21:06 Multi-Column Constraints and Null Values Phillip Tornroth <ptornroth@intellidot.net>
2006-04-29 20:14 ` Jeff Frost <jeff@frostconsultingllc.com>
2006-04-29 20:27 ` Bruno Wolff III <bruno@wolff.to>
2006-04-29 22:02 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-04-30 02:48 ` Tornroth, Phill <ptornroth@intellidot.net>
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