two fk's comprising a pk in a join table

[ken_s2007]

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

[comment]

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.

[Fenton]

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.

[comment]

Isn't this argument just as valid against primary keys in any table?

[Fenton]

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.

[coconutt2000]

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.

[T-Square]

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

[RalphL]

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.

[comment]

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).

[T-Square]

Ralph--

How would an AND relationship ensure unique entries in the join table?

And can you provide the link to the other thread you mentioned?

David