Can a field be a primary key as well as a foreign key?? 1

[notageek7]

I'm trying to find out if I can have a field, AdjustmentType which is in a table called Opportunities, as part of a primary key and also a foreign key.

For example:

Opp_ID VARCHAR2 (255),
AdjustmentType NUMBER,
CONSTRAINT OPPORTUNITIES_PK PRIMARY KEY(Opp_ID, AdjustmentType)


ALTER TABLE OPPORTUNITIES ADD
CONSTRAINT SFDCCMS_OPPORTUNITIES_FK5
FOREIGN KEY (AdjustmentType)
REFERENCES ADJUSTMENTTYPE(MS_Key);

 

[LKBrwnDBA]

Yes.


----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[SantaMufasa]

And if you want to achieve the same result in one CREATE TABLE command, you can say:

CREATE TABLE GEEK (
    Opp_ID                VARCHAR2 (255), 
    AdjustmentType constraint SFDCCMS_OPPORTUNITIES_FK5
        references ADJUSTMENTTYPE(MS_Key),
    CONSTRAINT OPPORTUNITIES_PK PRIMARY KEY(Opp_ID, AdjustmentType)
);

Table created.

Notice that you needn't explicitly specify data type for Foreign Keys since they assume the data type of the Primary Keys to which they point.

Let us know if you have questions.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.