|
Home > Archive > PostgreSQL Discussion > April 2005 > Re: PRIMARY KEY on a *group* of columns imply that each column is NOT
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Re: PRIMARY KEY on a *group* of columns imply that each column is NOT
|
|
| Stephane Bortzmeyer 2005-04-26, 8:23 pm |
| On Tue, Apr 26, 2005 at 03:22:40PM -0500,
Guy Rouillier <guyr@masergy.com> wrote
a message of 37 lines which said:
> "The primary key constraint specifies that a column or columns of a
> table may contain only unique (non-duplicate), nonnull values.
> Technically, PRIMARY KEY is merely a combination of UNIQUE and NOT
> NULL"
>
> Primary key columns cannot contain null values.
I read the above also. It is perfectly clear for primary key on one
column.
But it does not apply to primary keys containing a group of
columns. In that case (my case), columns do not have to be UNIQUE. But
they have to be NOT NULL, which puzzles me.
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql
.org so that your
message can get through to the mailing list cleanly
| |
| Patrick TJ McPhee 2005-04-27, 3:23 am |
| In article <20050426203938.GA18628@nic.fr>,
Stephane Bortzmeyer <bortzmeyer@nic.fr> wrote:
% But it does not apply to primary keys containing a group of
% columns. In that case (my case), columns do not have to be UNIQUE. But
% they have to be NOT NULL, which puzzles me.
It does apply to primary keys containing groups of columns.
You can get the table definition you want by using a unique constraint,
but you should know that in SQL, unique constraints don't apply to
rows containing null values in the constrained columns. If you
do this:
create table x (
name TEXT NOT NULL,
address INET,
CONSTRAINT na UNIQUE (name, address)
);
your table definition will be as you want it, but the constraint you
want won't be there.
$ INSERT INTO x VALUES ('alpha');
INSERT 194224 1
$ INSERT INTO x VALUES ('alpha');
INSERT 194225 1
$ INSERT INTO x VALUES ('alpha');
INSERT 194226 1
$ INSERT INTO x VALUES ('alpha');
INSERT 194227 1
--
Patrick TJ McPhee
North York Canada
ptjm@interlog.com
| |
| Stephane Bortzmeyer 2005-04-27, 3:23 am |
| On Tue, Apr 26, 2005 at 03:48:44PM -0500,
Scott Marlowe < smarlowe@g2switchwor
ks.com> wrote
a message of 26 lines which said:
> Here's a quote from the SQL1992 spec that's VERY clear:
Yes, PostgreSQL is right and implement the standard. Now, what's the
rationale for the standard? I understand it for a single column but,
for several columns, it should be still possible to have different
tuples, such as (3, NULL) and (5, NULL) for instance.
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql
.org
| |
| Stephane Bortzmeyer 2005-04-27, 9:23 am |
| On Wed, Apr 27, 2005 at 05:19:32AM +0000,
Patrick TJ McPhee <ptjm@interlog.com> wrote
a message of 37 lines which said:
> but you should know that in SQL, unique constraints don't apply to
> rows containing null values
May be I should but I didn't.
> your table definition will be as you want it, but the constraint you
> want won't be there.
OK, I will try to write a custom trigger, then.
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
| |
| Tom Lane 2005-04-27, 9:23 am |
| Stephane Bortzmeyer <bortzmeyer@nic.fr> writes:
> Yes, PostgreSQL is right and implement the standard. Now, what's the
> rationale for the standard? I understand it for a single column but,
> for several columns, it should be still possible to have different
> tuples, such as (3, NULL) and (5, NULL) for instance.
If that's what you want, declare it as UNIQUE not PRIMARY KEY.
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
| |
| Stephane Bortzmeyer 2005-04-27, 9:23 am |
| On Wed, Apr 27, 2005 at 10:26:30AM -0400,
Tom Lane <tgl@sss.pgh.pa.us> wrote
a message of 9 lines which said:
> If that's what you want, declare it as UNIQUE not PRIMARY KEY.
As shown by Patrick TJ McPhee, it does not work:
tests=> create table x (
tests(> name TEXT NOT NULL,
tests(> address INET,
tests(> CONSTRAINT na UNIQUE (name, address)
tests(> );
NOTICE: CREATE TABLE / UNIQUE will create implicit index "na" for table "x"
CREATE TABLE
tests=> INSERT INTO x (name) values ('foobar');
INSERT 45380 1
tests=> INSERT INTO x (name) values ('foobar');
INSERT 45381 1
tests=> INSERT INTO x (name) values ('foobar');
INSERT 45382 1
tests=> INSERT INTO x (name) values ('foobar');
INSERT 45383 1
tests=> select * from x;
name | address
--------+---------
foobar |
foobar |
foobar |
foobar |
(4 rows)
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql
.org
| |
| Stephane Bortzmeyer 2005-04-27, 11:23 am |
| On Wed, Apr 27, 2005 at 04:50:23PM +0200,
Sebastian Böck < sebastianboeck@freen
et.de> wrote
a message of 48 lines which said:
> CREATE UNIQUE INDEX na ON x (name, address) WHERE address IS NULL;
No, because it prevents two tuples with the same value of "name".
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
| |
| Stephane Bortzmeyer 2005-04-27, 11:23 am |
| On Wed, Apr 27, 2005 at 09:36:57AM -0500,
Scott Marlowe < smarlowe@g2switchwor
ks.com> wrote
a message of 18 lines which said:
> Often the best bet here, btw, is to declare it not null then use
> something other than null to represent null, like the text
> characters NA or something.
Yes, but it defeats the purpose of NULL. And what should I use as a
"pseudo-NULL" value for INET? 127.0.0.1? 0.0.0.0? Special values are
well-known for the problems they raise. That's why many languages have
NULL-like solutions (None in Python, undef in Perl, Maybe types in
Haskell, etc).
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
| |
| Stephane Bortzmeyer 2005-04-27, 11:23 am |
| On Wed, Apr 27, 2005 at 05:04:07PM +0200,
Sebastian Böck < sebastianboeck@freen
et.de> wrote
a message of 24 lines which said:
One is enough :-)
vvvvv
> CREATE TABLE table x (
> name TEXT NOT NULL,
> address INET
> );
>
> CREATE UNIQUE INDEX na ON x (name, address);
> CREATE UNIQUE INDEX n ON x (name) WHERE address IS NULL;
Great! It works fine. Many thanks.
tests=> select * from x;
name | address
------+---------
foo |
foo | 1.2.3.4
foo | ::1
bar | ::1
bar |
(5 rows)
tests=> insert into x (name) values ('bar');
ERROR: duplicate key violates unique constraint "n"
tests=> insert into x (name, address) values ('bar', '::1');
ERROR: duplicate key violates unique constraint "na"
tests=> insert into x (name) values ('new');
INSERT 21128 1
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
| |
| Stephane Bortzmeyer 2005-04-28, 9:23 am |
| On Thu, Apr 28, 2005 at 02:04:29PM +0200,
Marco Colombo <pgsql@esiway.net> wrote
a message of 146 lines which said:
> No. NULL is NOT 'None', nor 'undef', and definitely not NULL as in
> C.
Thanks for the very good and detailed explanation of NULL in
SQL. Curious people may note that the strange semantics of NULL are
heavily criticized in C. J. Date and Hugh Darwen "A Guide to the SQL
Standard" (Addison-Wesley) [Side note: I was perfectly aware for NULL
in C, where it is just an ordinary zero, that's why I only mentioned
Perl and Python.]
> marco=# select 2 = NULL;
> ?column?
> ----------
>
> (1 row)
Even better, you can write;
registry=> select NULL = NULL;
?column?
----------
(1 row)
> Depending on what you're trying to achieve, you may need to split
> the table (normalization the theorists call it).
Yes, I noticed in similar organizations that the Hosts table was split
in one table for names and one for addresses, may be for exactly that
reason.
> Review your design, maybe either the table schema or the choice of
> the primary key is not natural for your database.
At the present time, it seems that, practically speaking, the
technique proposed by Sebastian Böck (two index) is perfectly
fine. This technique can be summarized as "PostgreSQL, I tell you to
treat all NULL addresses as being the same value".
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
|
|
|
|
|