2 Replies Latest reply: Aug 27, 2009 8:18 AM by Karthick_Arp RSS

    Question on Foreign Key

    SID3
      Hi,

      I have a question on foreign key. When we create an index on a foreign key the index name doesnt get displayed in the index_name column of dba_constraints dd.

      But the associated index name for the primary key is displayed.

      Why so?

      Thanks in advance.
        • 1. Re: Question on Foreign Key
          94799
          Because the index is not associated with it?

          A primary or unique constraint either implicitly adopts a suitable existing index or creates its own.

          A foreign key does neither, although it is generally beneficial that foreign keys are indexed for query performance and to avoid some locking scenarios.
          • 2. Re: Question on Foreign Key
            Karthick_Arp
            Unique or Primary Key cannot enforce Uniqueness of a column with out a Index on that column. Hence Oracle will create an Index by default if an index does not exist on that column while createing these two constraints. So an index is always associated with these two constraints.

            But a foreign key does not enforces uniqueness on a column. And an Index on a foreign key is not mandatory. So even if you create an index on a foreign key column that index is not associated with the foreign key.