Re: Primary Key Problems

I did have this problem in the past. I have a table with three fields
set for the primary key. Somehow I would get dups in that table. I'm
not sure how as I am too busy to test it. I changed my insert queries
to check for existing data before the insert so I could get back to
work.

Someone did reply that it was from a bug in vacuum. I run vacuum each
night on my database.

I'm running:
PostgreSQL 7.1.3 on i686-pc-linux-gnu, compiled by GCC 2.95.4
from Debian packages
On:
Linux 2.4.17 #1 SMP Sat Dec 22 09:18:33 PST 2001 i686 unknown
Debian Sid (unstable) and Woody (testing).

Hope this helps.

|On 020114 13:01
|steve boyle (boylesa(at)dial(dot)pipex(dot)com) wrote the following...
|
|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?
--
BOFH Reason of the day: Maintence window broken
_ | _
(_()(|('.|)('||.|()|`|(