how to use foreign keys as primary key

Question

i have a table with two foreign keys as composite key.

ActivityTbl -

(activityNbr(PK), supervisor(FK), status, type, startDate, endDate, location )

VolunteerTbl -

(volunteerNbr(PK), name, address, contact)

Now I created a table where volunteer's choices can be stored as per their prefereance rating.

ActivityChoice

(activityNbr(FK), VolunteerNbr(FK), Rating)

So the combination of those 2 FKs make a composite key. I am using sql Server to create a table.

Create ActivityChoiceTbl(
     VolunteerNbr   int NOT NULL,
     ActivityNbr    int NOT NULL,
     Rank           int NOT NULL,
     CONSTRAINT PKActivityChoice    PRIMARY KEY (VolunteerNbr,ActivityNbr), 
     CONSTRAINT CKRank CHECK (Rank>0 AND Rank<=9));

So in this case do I need to add another foreign key constrain for both to mention that they are foreign keys?? Am I doing it right?? Thanks

So just to check, `ActivityChoiceTbl` with the `Rank` column is a different table to the `ActivityChoice` one with the `Rating` column? — Martin Smith, Sep 16 '11 at 08:36

Mike Sherrill 'Cat Recall' · Accepted Answer · 2011-09-16T10:34:25.953

Yes, you need two foreign key constraints. The most direct way in standard SQL is to immediately reference the table.

Create ActivityChoiceTbl(
     VolunteerNbr   int NOT NULL REFERENCES VolunteerTbl (volunteerNbr),
     ActivityNbr    int NOT NULL REFERENCES ActivityTbl (activityNbr),
     Rank           int NOT NULL,
     CONSTRAINT PKActivityChoice    PRIMARY KEY (VolunteerNbr,ActivityNbr), 
     CONSTRAINT CKRank CHECK (Rank>0 AND Rank<=9));

But adding two more constraint clauses lets you name the constraints, which is a better practice.

Create ActivityChoiceTbl(
     VolunteerNbr   int NOT NULL,
     ActivityNbr    int NOT NULL,
     Rank           int NOT NULL,
     CONSTRAINT PKActivityChoice    PRIMARY KEY (VolunteerNbr,ActivityNbr), 
     CONSTRAINT FKActivityChoiceVolunteerNbr 
         FOREIGN KEY (VolunteerNbr) REFERENCES VolunteerTbl (VolunteerNbr),
     CONSTRAINT FKActivityChoiceActivityNbr 
         FOREIGN KEY (ActivityNbr) REFERENCES ActivityTbl (ActivityNbr),
     CONSTRAINT CKRank CHECK (Rank>0 AND Rank<=9));

If ActivityChoice is a separate table that needs to reference ActivityChoiceTbl, then you also need something along these lines.

CREATE TABLE ActivityChoice (
    VolunteerNbr INTEGER NOT NULL,
    ActivityNbr INTEGER NOT NULL,
    Rating  DECIMAL (2,1) NOT NULL CHECK (Rating between 0 and 9),  -- Pure guess
    PRIMARY KEY (VolunteerNbr, ActivityNbr),
    FOREIGN KEY (VolunteerNbr, ActivityNbr) 
      REFERENCES ActivityChoiceTbl (VolunteerNbr, ActivityNbr)
);

Or the OP might need a single composite column FK constraint from `ActivityChoiceTbl` to `ActivityChoice` depending on how you interpret the question. — Martin Smith, Sep 16 '11 at 09:44
@Martin Smith: Good point. I thought there was just one table with a composite key, but there might be two. I'll wait for the OP to clarify. — Mike Sherrill 'Cat Recall', Sep 16 '11 at 09:52
You can add a named column constraint directly at the column's definition: `CREATE TABLE … (…, SomeFK int CONSTRAINT FK_ThisTable_ThatTable REFERENCES ThatTable (SomeID), …)`. — Andriy M, Sep 16 '11 at 11:57
@catcall I ended up writing the second script catcall suggested. (with composit key as primary key and applying 2 Tavel level constraints on the foreign keys) seems to do the job for me. Thanks for the suggestion. — , Sep 16 '11 at 14:50

score 0 · Answer 2 · answered Sep 16 '11 at 08:58

I would suggest you have a separate single PrimaryKey in ActivityChoice table. Make VolunteerNbr and ActivityNbr foreign keys and add a composite unique key constraint for two columns VolunteerNbr, ActivityNbr.

Some of the viewpoints you would like to take a look at, about composite foreign key. http://www.upsizing.co.uk/Art10_MultiPartkeys.aspx

http://social.msdn.microsoft.com/Forums/en/transactsql/thread/158d77f7-3029-43bc-bba6-a8a12374f00c

how to use foreign keys as primary key

2 Answers2