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@dial.pipex.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@nsi.edu> wrote in message
|news:f40d3195.0112281441.223a73ce@posting.google.com...
|> pgsql@c0de.net (Phill Kenoyer) wrote in message
|news:<20011208014433.GA2913@c0de.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
                                               _   |       _
                                              (_()(|('.|)('||.|()|`|(