PostgreSQL Discussion - Re: PRIMARY KEY on a *group* of columns imply that each

Author

Re: PRIMARY KEY on a *group* of columns imply that each

Scott Marlowe

2005-04-26, 8:23 pm

On Tue, 2005-04-26 at 15:39, Stephane Bortzmeyer wrote:
> On Tue, Apr 26, 2005 at 03:22:40PM -0500,
> Guy Rouillier <guyr@masergy.com> wrote
> a message of 37 lines which said:
>
>
> 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.

Here's a quote from the SQL1992 spec that's VERY clear:

A unique constraint is satisfied if and only if no two rows in
a table have the same non-null values in the unique columns. In
addition, if the unique constraint was defined with PRIMARY KEY,
then it requires that none of the values in the specified column or
columns be the null value.

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Richard Huxton

2005-04-27, 3:23 am

Stephane Bortzmeyer wrote:
> On Tue, Apr 26, 2005 at 03:48:44PM -0500,
> Scott Marlowe < smarlowe@g2switchwor
ks.com> wrote
> a message of 26 lines which said:
>
>
>
>
> 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.

The value of (3,NULL) isn't well-defined. In particular, you can't say
that (3,NULL) = (3,NULL) since NULL means not-known. The fact that part
of the value is not known means the value as a whole is not known.

--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Sebastian Böck

2005-04-27, 11:23 am

Stephane Bortzmeyer wrote:
> 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:
>
>
>
>
> 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)
>

If i understand correctly, you want something like:

create table x (
name TEXT NOT NULL PRIMARY KEY,
address INET
);

CREATE UNIQUE INDEX na ON x (name, address) WHERE address IS NULL;

HTH

Sebastian

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere
" to majordomo@postgresql
.org)

Sebastian Böck

2005-04-27, 11:23 am

Stephane Bortzmeyer wrote:
> 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:
>
>
>
>
> No, because it prevents two tuples with the same value of "name".

Ahh, sorry! Ment something more like:

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;

HTH

Sebastian

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Sponsored Links