Re: all foreign key should have index?
- From: Eric Junkermann <eric@xxxxxxxxxxxxxxxxx>
- Date: Mon, 30 Jan 2006 18:46:57 +0000
In message <m3irs1hfsw.fsf@xxxxxxxxxxxxxxxxxxxxxxx>, Christopher Browne <cbbrowne@xxxxxxx> writes
Is it necessary that all foreign key should have index?
It is not necessary from a theoretical standpoint.
It is necessary, from a practical standpoint, if the table pointed to is fairly large, and you want to be able to validate the foreign key references in a reasonable period of time.
SELECT * FROM user_constraints WHERE CONSTRAINT_TYPE = 'R' AND INDEX_NAME IS NOT NULL
Above query does not return any row in my database. Is it bad?
I don't know. Did you create a table in your database called "user_constraints?" That's not one of the standard view names in the SQL-92 INFORMATION_SCHEMA schema, so it's certainly not something that could be considered "standard."
Talking about Oracle by the look of it - in which case that query always returns nothing, the INDEX_NAME column in USER_CONSTRAINTS is used only to identify the index used to enforce a primary or unique key.
If there is no index on the foreign key column(s) in the child table, then Oracle will lock the entire child table for a delete or primary key update on the parent table - so you often, but not always, want the index, apart from the performance issue referred to above.
To check if a suitable index exists you will have to look in USER_IND_COLUMNS.
--
Eric Junkermann
.
- References:
- all foreign key should have index?
- From: Murtuja
- all foreign key should have index?
- Prev by Date: Re: Reminder, blatant ad
- Next by Date: Re: Reminder, blatant ad
- Previous by thread: all foreign key should have index?
- Next by thread: Re: all foreign key should have index?
- Index(es):
Relevant Pages
|
