foreign key constraints and indexes

foreign key constraints and indexes

Post by ayrobin » Sun, 02 Dec 2001 03:19:06



Hi,

Sorry if this question was asked before.

Some databases create non-unique indexes when a foreign key constraint is
created.  i.e.
create table test2
(id number, id2 number unique, constraint fk_test2 FOREIGN KEY (id)
references test(id));

So a non unique index would be created on id in test2.
Oracle does not seem to do this.

However, it does this for primary keys and unique constraints.

Is this a configurable thing?  Can i make oracle do it automatically or do i
need to manually create
the index.

thanx in advance.

 
 
 

foreign key constraints and indexes

Post by Jim Kenned » Sun, 02 Dec 2001 03:20:47


You need to create the index.  If you don't you will lock the parent table
when you insert.

Jim

Quote:> Hi,

> Sorry if this question was asked before.

> Some databases create non-unique indexes when a foreign key constraint is
> created.  i.e.
> create table test2
> (id number, id2 number unique, constraint fk_test2 FOREIGN KEY (id)
> references test(id));

> So a non unique index would be created on id in test2.
> Oracle does not seem to do this.

> However, it does this for primary keys and unique constraints.

> Is this a configurable thing?  Can i make oracle do it automatically or do
i
> need to manually create
> the index.

> thanx in advance.


 
 
 

foreign key constraints and indexes

Post by Vincent Ventron » Wed, 05 Dec 2001 05:34:04


"> Some databases create non-unique indexes when a foreign key constraint is

Quote:> created.  i.e.
> create table test2
> (id number, id2 number unique, constraint fk_test2 FOREIGN KEY (id)
> references test(id));

> So a non unique index would be created on id in test2.
> Oracle does not seem to do this.

No way to make it happen automatically but it would be a good idea to create
indexes on all FK columns -- both to improve JOIN performnace & also to
avoid locking parent tables when you do DML on the children.
 
 
 

foreign key constraints and indexes

Post by ayrobin » Fri, 07 Dec 2001 01:24:14


yeah, DBs like cloudscape (informix) automatically creates indexes on
foreign keys.
So i erroneously thought oracle does the same.

thanx


Quote:

> "> Some databases create non-unique indexes when a foreign key constraint
is
> > created.  i.e.
> > create table test2
> > (id number, id2 number unique, constraint fk_test2 FOREIGN KEY (id)
> > references test(id));

> > So a non unique index would be created on id in test2.
> > Oracle does not seem to do this.

> No way to make it happen automatically but it would be a good idea to
create
> indexes on all FK columns -- both to improve JOIN performnace & also to
> avoid locking parent tables when you do DML on the children.

 
 
 

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. Strange Fragmentation Problem

3. Ref.Constraint: Index on Foreign Key?

4. HELP! Can't start SQLServer. Error.

5. Ad: Programmers Wanted

6. Index on foreign key constraint

7. Datagrid and Record set

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 ?