Creating Foreign Key constraint

I have two tables; each has multi-column primary keys. I need to create a foreign key relationship between the two tables.

Sales:

DKey, int, PK

OKey,int, PK

RKey,int, PK

...

Rep:

Bkey, int, PK, identity

Rkey, int, PK

...

When I try to like Rkey from both tables as a foreign key relationship, I get an error that the columns in one table "do not match an existing primary key or UNIQUE constraint. (I've tried it both ways and get the same error.)

How can I link these two tables?

Thanks.

Make an unique index on Rkey from Rep or Sales depends on the table you can delegate "Primary Key" (use Database Diagram for usability)

|||

Since you did not include rationale for the key selection, this is going to be just a guess.

The purpose of a Primary Key is to uniquely identify a row of data. It looks like each Rep is uniquely identified with the IDENTITY field BKey. I'm not sure why you have RKey also involved in the Primary Key for Rep.

You could recreate the Primary key for Rep using only the Bkey, and then add the Bkey field to Sales to relate Sales to Rep.

OR, if RKey is unique, use it as a Primary Key.

However, if for some silly reason you HAVE to have a combination of Bkey and Rkey as the Primary key in Rep, you will have to add both columns to Sales in order to create the relationship.

As a side note, the use of Bkey, Rkey, etc. for column names is very 'odd', and not only adds confusion, but will be difficult to maintain the pattern for all tables. A 'best practice' naming convention for an IDENTITY field (such as Bkey) is to use the table name and the suffix [ID]. So a preferred name for Bkey would be RepID. The name instantly communicates where the field comes from and what it is, expecially useful when it is added to another table.

|||

I was trying to simplify the question....I probably should have included more details. I'm sorry, I never know how much detail to go into.

What I'm actually trying to do is create a many to many link between:

Sales:

DateKey, int, PK

OrganizationKey, int, PK

CustomerKey, int, PK

RepBridgeKey, int, PK

....

RepBridge:

RepBridgeKey, int, PK, identity

RepKey, int, PK

Rep:

RepKey, int, PK, identity

.....

I can link RepBridge and Rep via RepKey but not RepBridge with Sales via RepBridgeKey.

From your answer, it looks like I would have to include RepKey in my Sales table and do the FK link on both fields?

Thanks.

|||

No, I think you can only make an unique index on RepBridgeKey from RepBridge (is possible because is identity)

You can make the following links vis-a-vis your vision of table schema:

Rep <RepKey-->RepBridge

RepBridge <RepBridgeKey>Sales

|||

John,

In RepBridge, I'm still not sure why you need RepKey as part of the Primary Key. It seems like BridgeRepKey is a unique identifier and would adequately serve as the PK (and the FK in Sales).

RepBridgeKey 'should' be all you need.

However, if, as said earlier, there is a business reason to complicate the Primary key in BridgeRep by adding RepKey -then you will have to add RepKey to Sales -at which point, the RepBridge table seems unnecessary. You would have to duplicate the rows in Sales for each Rep if there are multiple Reps involved with a single Sale.

Your sales table probably needs a better defined primary key. Typically a unique sales identifier is created -something like an INVOICE number, SalesOrder number, etc. In your Sales table above, only one sale would be allowed per day per Rep. That may be reasonable, but is it realistic? I would prefer a [SalesID int IDENTITY Primary Key] field

Typically, a 'bridging' table will have an IDENTITY field as PK, and the PK's from BOTH others tables as FK.

RepBridge

RepBridgeKey, int IDENTITY PK

RepKey FK to Rep

SalesKey Fk to Sales

|||

I see what you're saying....the bridge table does not need the RepKey as part of its primary key. I was thinking that each new sales rep would have to trigger a new bridge record but this can be done with only one PK identity field.

There is a reason behind the key fields in the sales table, but perhaps I shall also revisit that side issue.

Thanks again.