Re: Primary Key Problems

Tony / Phil,

my 2c on the Primary Key definition / usage. If you have any SQL / DDL code
that disagrees with the definitions below could you please repost them.

"Tony Reina" <reina(at)nsi(dot)edu> wrote in message
news:f40d3195(dot)0112281441(dot)223a73ce(at)posting(dot)google(dot)com(dot)(dot)(dot)
> pgsql(at)c0de(dot)net (Phill Kenoyer) wrote in message
news:<20011208014433(dot)GA2913(at)c0de(dot)net>...
> > Here is a good one. I have three fields set for my primary key. Now I
> > thought that a primary key was unique, and dups can not be inserted.
> >
>
> I don't think primary keys per se are unique, but rather can be made
> unique by specifying that option. IIRC primary keys just allow you to
> index searches within the database. So to make a unique primary key
> from your db schema:
>

The definition of a primary key was that it WAS a unique identifier for the
table

Definition: The primary key of a relational table uniquely identifies each
record in the table. It can either be a normal attribute that is guaranteed
to be unique (such as Social Security Number in a table with no more than
one record per person) or it can be generated by the DBMS (such as a
globally unique identifier, or GUID, in Microsoft SQL Server).

Ref: http://databases.about.com/library/glossary/bldef-primarykey.htm

Also

PRIMARY KEY

This column is a primary key, which implies that uniqueness is enforced by
the system and that other tables may rely on this column as a unique
identifier for rows. See PRIMARY KEY for more information.

AND

The PRIMARY KEY column constraint specifies that a column of a table may
contain only unique (non-duplicate), non-NULL values. The definition of the
specified column does not have to include an explicit NOT NULL constraint to
be included in a PRIMARY KEY constraint.

Ref:

http://www.ninthwonder.com/info/postgres/user/sql-createtable.htm

Regards

sb

>
> CREATE TABLE "inventory" (
> "stock" character varying(50) NOT NULL,
> "inventory_type" character varying(20) DEFAULT 'unknown' NOT
> NULL,
> "client_id" integer NOT NULL,
> [...]
> UNIQUE ("stock", "inventory_type", "client_id")
> Constraint "inventory_pkey"
> Primary Key ("stock", "inventory_type", "client_id")
> );
>
> HTH,
> -Tony
>
>
> BTW, I'm using Google newsgroups to view the Postgres messages, but
> haven't seen posted messages in several days on the Hackers list. Does
> anyone know if this is a Google glitch or is the hackers list just not
> very active during the holiday?