Is Foreign Key an Index as well?

From: <aravind.kanda_at_gmail.com>
Date: 29 May 2007 07:45:10 -0700
Message-ID: <1180449910.802581.210600@p47g2000hsd.googlegroups.com>

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. Received on Tue May 29 2007 - 09:45:10 CDT