| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.tools -> Re: Index and Constraint
Well everyone doesn't know that, because it isn't quite correct. Oracle does create an index for a primary key, if a unique one doesn't already exist on the table, but it does not automatically create an index for a foreign key. If you want one, you have to create it yourself, and it would very rare for it to be unique (except when there is a 1-1 relationship).
I am assuming that you aren't specifying a constraint name, because the index (Primary key) name will always match the key name.
SQLWKS> create table bob (a number primary key, b varchar2(20));
Statement processed.
SQLWKS> select constraint_name from user_constraints where table_name='BOB';
CONSTRAINT_NAME
2>
INDEX_NAME
If you happen to specify the primary key name it will still match.
SQLWKS> alter table bob drop constraint SYS_C00982
2>
Statement processed.
SQLWKS> alter table bob add constraint bobs_pk primary key (a);
Statement processed.
SQLWKS> select index_name from user_indexes where table_name='BOB'
2>
INDEX_NAME
I hope that helps.
-- Robert Fazio, Oracle DBA rfazio_at_home.com remove nospam from reply address http://24.8.218.197/ "Robert Chung" <robertch_at_mindspring.com> wrote in message news:39256c4f.661295640_at_news.mindspring.com...Received on Sat May 20 2000 - 00:00:00 CDT
>
> As everybody knows, Oracle creates unique index automatically when
> primary key or foreign key constraints are created on table. They can
> be checked by looking up DBA_INDEXES, ALL_INDEXES, etc., but I cannot
> find any info that shows which index is associated with which
> constraint, especially when index is created first and constraint is
> added later with different names. Is there any way to find that out
> without looking up DBA_IND_COLUMNS and DBA_CONS_COLUMNS and comparing
> columns manually? Thank you in advance.
>
>
>
>
 |
 |