Jump to content

two fk's comprising a pk in a join table


ken_s2007
 Share

This topic is 5699 days old. Please don't post here. Open a new topic instead.

Recommended Posts

I've read in several books that every table must have a primary key, but I've also read that if a join table has two foreign keys that uniquely identify each record, that a separate primary key isn't required. I believe that by this, it is meant that only the two fk's are needed, and not a third field that is a calculation -- concatenated text field of the two fk's. Just wanted to make sure I understand this correctly.

Ken

Link to comment
Share on other sites

A separate primary key is not required for the basic join to work, but it may required for some more sophisticated features, so it's best to have it there right from the start.

In any case, the primary key should be an auto-entered serial number - NOT a concatenation of the two foreign keys.

Link to comment
Share on other sites

An argument against a primary key in join tables, unless required, is that it then must be reset after importing into a clone, such as when restoring a crashed file, and possibly after other types of synchronization. Yes, a rare situation, but not as rare as one would think.

Link to comment
Share on other sites

I was talking about a simple join table, which may have nothing but the 2 foreign keys. A primary ID is not "required" for basic use.

Almost all other tables would have a primary ID, because it would be used, as either a relational or self-relational key.

Link to comment
Share on other sites

I'm with Comment on having the primary key(s) in the join table. At least while you're designing the db. If at the end of the design process you find you haven't used the primary key for the join table, you could delete it and conform to Fenton's opinion.

It is always much easier to have a serial primary key there already than to add one later. And it is always simple enough to remove.

Link to comment
Share on other sites

Ken--

What you've been reading about is the ability in other RDBMS packages to use multiple fields to designate a unique entry in a join table. This functionality is not present in Filemaker, which is why it seems out of place.

The reason you'd use it is to ensure that duplicate combinations didn't creep into your dataset. Using the combined fields to define unique records ensures that you cannot have two entries linking Item 1 and Category B. The ability to do this makes data management extremely easy, as you do not have to perform a set of (sometimes complex) checks to determine whether an entry is unique.

Comment and coconut's idea of adding a third field that is a unique number makes sense in the Filemaker world, but doesn't address the question of establishing truly unique entries (an issue that has been discussed at length in these forums).

David

Link to comment
Share on other sites

I see a lot of 6-think in these answers. In FMP 7 & 8 I see no reason for a separate primary key. Use the 2 foreign keys in an AND relationship for any other relationships join table.

Look at the sample file I have posted on the many to many relationships.

Link to comment
Share on other sites

I see a lot of 6-think in these answers.

Quite the contrary. As I said, a primary key is not required for the BASIC join. But I have posted several demos where such key was put to good use in filtering the join table (such as a portal summarizing line items by product).

Link to comment
Share on other sites

This topic is 5699 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.