Index on foreign key constraint

Index on foreign key constraint

Post by Red Valse » Sun, 31 Dec 1899 09:00:00



Why does Informix create a duplicate index on columns used in
foreign-key constraints?
 
 
 

Index on foreign key constraint

Post by mcoll.. » Sun, 31 Dec 1899 09:00:00


Quote:> Why does Informix create a duplicate index on columns used in
> foreign-key constraints?

To make it easier to check for child rows when deleting rows from the parent table.
 Without these indexes, Informix would have to perform a sequential scan of the
child table anytime there was a delete in the parent table to see if there were any
matching rows in the child table.  It would then either reject the parent row
delete or cause cascading deletes in the child table, depending on the foreign key
constraint definition.

Mark Collins


 
 
 

Index on foreign key constraint

Post by Richard Auslande » Sun, 31 Dec 1899 09:00:00


Red -

It shouldn't make a duplicate.  Something must be different about the
one you generated and the one it generated.  TRY THIS:  Generate the
needed index yourself *first*, and then install the foreign key
constraint with "alter table add constraint...".  It should definitely
work this way.


> Why does Informix create a duplicate index on columns used in
> foreign-key constraints?

--
Richard C. Auslander
Database Manager

AirFlash, Inc.
1733 Woodside Rd., Suite #110
Redwood City, CA   94061
(650) 556-7928

www.airflash.com

 
 
 

Index on foreign key constraint

Post by Hal Mane » Sun, 31 Dec 1899 09:00:00


If that column can be null, and since there could then be multiple nulls in
the table, having an index with duplicates would make sense.  Nulls in a
foreign key column are allowed (not only in Informix) so that you can store
rows where the foreign key constraint may not apply...

>Why does Informix create a duplicate index on columns used in
>foreign-key constraints?

 
 
 

1. 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

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

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

2. Help! pipes vs tcp/ip

3. foreign key constraints and indexes

4. MS SQL NewsGroup

5. Ref.Constraint: Index on Foreign Key?

6. run menu gone in foxpro mac

7. datapump/instalshield

8. Check Constraint vs. Foreign Key Constraint

9. Changing Primary Key Values when foreign key constraints exist

10. BCP INTO A TABLE THAT CONTAINS PRIMARY KEY AND FOREIGN KEY CONSTRAINTS

11. Q: foreign key constraint on partial primary key ?