Re: Primary Key Problems

On Saturday 08 December 2001 02:44, Phill Kenoyer wrote:
> 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.

That's the theory ;-)

> CREATE TABLE "inventory" (
>         "stock" character varying(50) NOT NULL,
>         "inventory_type" character varying(20) DEFAULT 'unknown' NOT NULL,
>         "client_id" integer NOT NULL,
> [...]
>         Constraint "inventory_pkey"
>         Primary Key ("stock", "inventory_type", "client_id")
> );
>
> and I insert into the db and get dups.
>
>  client_id | stock | inventory_type
> -----------+-------+----------------
>         81 | 2001  | new
>         81 | 2001  | new
>         81 | 2003  | new
>         81 | 2005  | new
>         81 | 2006  | new
>         81 | 2006  | new
>         81 | 2008  | new
>         81 | 2008  | new
>
> Why did I get dups in my data?

Are the duplicated values in "stock" and "inventory_type"
genuinely identical? As they are both VARCHARs they could
contain differing amounts of trailing spaces or other
'invisible' characters which would not be obvious from the
console, but would represent valid and unique values
to the constraint checker. These could result from
unfiltered input, such as from an HTML form.

Also, does the primary key still exist?

SELECT relname, relowner FROM pg_class WHERE relname='inventory_pkey'

Ian Barwick