Re: Foreign key constraint generates unique index?

That probably comes from the SERIAL definition, not the fk.  Since serial
is defined to give unique values (well up till you run out anyway), a
unique constraint makes sense.  You probably meant owner_id int4
constraint ... since you don't want automatic values being inserted.

On Fri, 18 May 2001, Jeff Boes wrote:

> This is a puzzle: I'm creating a small table that will have a single
> column primary key, and a foreign key referencing another table. Here's
> the SQL:
>
> CREATE TABLE dogs
> (
>     dog_id          SERIAL      CONSTRAINT ix_dogs_pk PRIMARY KEY,
>     owner_id        SERIAL      CONSTRAINT ix_dogs_1 REFERENCES owners
>                                     ON DELETE CASCADE,
>     name            TEXT
> );
>
> When I run this in psql 7.1, I get:
>
> NOTICE:  CREATE TABLE will create implicit sequence 'dogs_dog_id_seq' for
>   SERIAL column 'dogs.dog_id'
> NOTICE:  CREATE TABLE will create implicit sequence 'dogs_owner_id_seq'
>   for SERIAL column 'dogs.owner_id'
> NOTICE:  CREATE TABLE/PRIMARY KEY will create implicit index 'ix_dogs_pk'
>   for table 'dogs'
> NOTICE:  CREATE TABLE/UNIQUE will create implicit index
>   'dogs_owner_id_key' for table 'dogs'
> NOTICE:  CREATE TABLE will create implicit trigger(s) for FOREIGN KEY
>   check(s)
> CREATE
>
> Why is Postgresql creating a unique index for 'dogs_owner_id_key'? I
> don't want that column to be unique!