Re: Is Foreign Key an Index as well?

aravind.kanda_at_gmail.com wrote:
> I have two tables TACCOUNT & TCUSTOMER joined by one to many. I have
> Account_sk in both tables which is used to join. When I create the
> account_sk in TCUSTOMER as a foreign key, is there any added advantage
> of having an index on the account_sk? Definitions of both tables are
> below:
>
> TCUSTOMER TABLE DEFINITION:
>
> CREATE TABLE TCUSTOMER
> (
> CUSTOMER_SK NUMBER(10) NOT NULL,
> CUSTOMER_ID CHAR(10 BYTE) NOT NULL,
> EFFECTIVE_DT DATE,
> EXPIRATION_DT DATE,
> ACCOUNT_SK NUMBER(10)
> );
>
> ALTER TABLE TCUSTOMER ADD (
> FOREIGN KEY (ACCOUNT_SK)
> REFERENCES TACCOUNT (ACCOUNT_SK)
> ON DELETE SET NULL);
>
> CREATE INDEX IDX_ACCOUNT_SK ON TCUSTOMER
> (ACCOUNT_SK);
>
> ALTER TABLE TCUSTOMER ADD (
> PRIMARY KEY (CUSTOMER_SK);
>
>
> ACCOUNT TABLE DEFINITION:
>
> CREATE TABLE TACCOUNT
> (
> EFFECTIVE_DT DATE,
> EXPIRATION_DT DATE,
> ACCOUNT_NM VARCHAR2(30 BYTE),
> ACCOUNT_SK NUMBER(10) NOT NULL,
> ACCOUNT_NO CHAR(10 BYTE)
> )
> ;
>
> CREATE UNIQUE INDEX IDX_TACCOUNT_ACCOUNT_NO ON TACCOUNT
> (ACCOUNT_NO);
>
>
> ALTER TABLE TACCOUNT ADD (
> PRIMARY KEY (ACCOUNT_SK)
>
>
> Many Thanks in Advance.
>

By default, there is no index on the FK columns. However, it is normally a good idea to index these columns. See the following:

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:292016138754
http://asktom.oracle.com/pls/asktom/f?p=100:11:29255896742093::::P11_QUESTION_ID:292016138754
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1156435031319

HTH,
Brian

-- 
===================================================================

Brian Peasland
dba_at_nospam.peasland.net
http://www.peasland.net

Remove the "nospam." from the email address to email me.


"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown

-- 
Posted via a free Usenet account from http://www.teranews.com