Foreign key constraint generates unique index?

From: Jeff Boes <jboes(at)nexcerpt(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Foreign key constraint generates unique index?
Date: 2001-05-18 14:44:20
Message-ID: 20010518104420.69c618ad.jboes@nexcerpt.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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(at)nexcerpt(dot)com

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2001-05-18 14:45:19 Re: Queries across multiple databases (was: SELECT from a table in another database).
Previous Message Bruce Momjian 2001-05-18 14:37:11 Re: Re: Performance aggregates