Foreign key constraint generates unique index?

Foreign key constraint generates unique index?

Post by Jeff Bo » Sun, 20 May 2001 00:14:30



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

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://www.postgresql.org/search.mpl

 
 
 

Foreign key constraint generates unique index?

Post by Jeff Bo » Sun, 20 May 2001 01:21:59


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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

 
 
 

Foreign key constraint generates unique index?

Post by Stephan Sza » Sun, 20 May 2001 02:47:41


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.


> 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!

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command

 
 
 

1. UNIQUE CONSTRAINTS & UNIQUE INDEXES/PRIMARY KEYS

The on-line books says the following about PRIMARY KEYS and UNIQUE
CONSTRAINTS

UNIQUE constraints enforce the uniqueness of the values in a set of columns.
No two rows in the table are allowed to have the same non NULL values for
the columns in a UNIQUE constraint.
Primary keys also enforce uniqueness, but primary keys do not allow NULL
values.
A UNIQUE constraint is preferred over a unique index.

But I tried to apply a UNIQUE CONSRAINT having removed the duplicate data,
with the following :-

ALTER TABLE TABLE1 WITH NOCHECK
ADD CONSTRAINT CONS1 UNIQUE (COL1)
ON [PRIMARY]

But it will not allow it while you have NULL values just as if I were
applying a UNIQUE INDEX even though I told it to create a constraint ?

Not only that if I remove the rows with NULL values and apply the UNIQUE
CONSTRAINT successfully,
it will not allow more than one row with a value of NULL to be inserted ?

Please can any explain?

Martin Speight.

2. MD-GAITHERSBURG-249602--Telecommunications-UNIX-ORACLE-C-Web Applications-Project Team Lead

3. drop boxes not populating

4. Only Unique Foreign Key Constraints in SQL Server?

5. How to GROUP on EXTRACTed field in Local SQL

6. unique names for foreign key constraints

7. Colors in ADO Datagrid ?

8. 7.1.2 and foreign key unique constraint.

9. Unique Constraints and Foreign Key

10. Foreign key to unique constraint

11. ERWin won't generate correct Oracle foreign key constraint

12. Generating the foreign key constraints