Обсуждение: Foreign key constraint generates unique index?

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!

--
Jeff Boes                                             vox 616.226.9550
Database Engineer                                     fax 616.349.9076
Nexcerpt, Inc.                                      jboes@nexcerpt.com

Regarding my just-sent message about the addition of a unique key
where I didn't want one...

Aha! I now realize that just declaring a column as SERIAL is what
triggers the addition of the UNIQUE constraint. I thought it had to do
withe the REFERENCES clause.

I was thinking of SERIAL as a data type, and that it was desirable to
declare the foreign key column as SERIAL so that it would have the
same base type. I'll have to declare it as INTEGER of course.

That raises the next question: ideally, I'd want to make sure that my
FK column retains the same base type as the column it's pointing
at. So, for example, a future PG version might implement SERIAL as a
BIGINT or something, and I'd want my FK to reflect that. Doesn't seem
possible.

--
Jeff Boes                                             vox 616.226.9550
Database Engineer                                     fax 616.349.9076
Nexcerpt, Inc.                                      jboes@nexcerpt.com

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!