Thread: multi-column primary key insert error ('duplicate' complaint)

I have the following table where I want to use two columns as the primary:

create table MOVIES (

id                      char(12)        unique references PRODUCTS,
volume_id               int2            not null default 1,
label_id                integer         references LABELS(id),
length                  int2            not null,

primary key(id, volume_id)
);


However when I try the following:

insert into movies values('589000901097','1','1','1');
insert into movies values('589000901097','2','1','1');

I get this error:

ERROR:  Cannot insert a duplicate key into unique index movies_id_key

I thought I had defined the primary key to span two columns ... the data
in the first two columns is not a duplicate...

What did I miss?

Thanks!

Jc

You have put a unique constraint on the column id so when you put two
identical id's into the system it will complain

You will have to remove the unique from the id and then you will be fine

HTH

On Mon, 5 Aug 2002, Jean-Christian Imbeault wrote:

> I have the following table where I want to use two columns as the primary:
>
> create table MOVIES (
>
> id                      char(12)        unique references PRODUCTS,
> volume_id               int2            not null default 1,
> label_id                integer         references LABELS(id),
> length                  int2            not null,
>
> primary key(id, volume_id)
> );
>
>
> However when I try the following:
>
> insert into movies values('589000901097','1','1','1');
> insert into movies values('589000901097','2','1','1');
>
> I get this error:
>
> ERROR:  Cannot insert a duplicate key into unique index movies_id_key
>
> I thought I had defined the primary key to span two columns ... the data
> in the first two columns is not a duplicate...
>
> What did I miss?
>
> Thanks!
>
> Jc
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>

--
Darren Ferguson

* Jean-Christian Imbeault wrote on Monday, 2002-08-05:

> create table MOVIES (
>
> id                      char(12)        unique references PRODUCTS,
                                          ^^^^^^

> volume_id               int2            not null default 1,
> label_id                integer         references LABELS(id),
> length                  int2            not null,
>
> primary key(id, volume_id)
> );
>
> ERROR:  Cannot insert a duplicate key into unique index movies_id_key
                                                          |      |
                                                 table name      |
                                                        field name
>
> I thought I had defined the primary key to span two columns ... the data
> in the first two columns is not a duplicate...

There is a unique index on "id" alone, in addition to the
participation in the primary key.

--
Christian Ullrich                   Registrierter Linux-User #125183

"Deliver."