Re: Ref.Constraint: Index on Foreign Key?

On Fri, 29 Jan 1999 08:50:05 +0100, Achim Reiners <areiners_at_template.de> wrote:

>
>If I construct a Referential Constraint,
>does Sybase/Oracle automatically implicit construct
>an Index on the Foreign Key column if not already exist?

No. You need to create the index yourself if you want it.

>If I was told correctly I wonder how the Ref.Constraint
>is efficiently checked at e.g. inserts/updates on the foreign key
>columns in sysbase and oracle.

You may want to create the indexes. A lot depends on the types of transactions that you are issueing. If you never, ever delete a parent or change a referenced column in the parent table, then you can get away without the index.

I worked on a relational database once that would let you create a primary key constraint without a corresponding index. From a conceptual, and theoretical view, that makes complete sense. It's a separation of logical and physical implementation. For large tables, insert performance would be terrible because the database would do a full tablescan for each new record in order to check primary key uniqueness. However, you had complete flexibility over how you chose to implement things.

Jonathan Received on Sat Jan 30 1999 - 19:12:05 CST