Primary key is the key field in the Parent table. The foreign key is the key field in the child table. The only reason to have both a primary key and foreign key is to establish a relationship. No relationship, no need for either field.
Awesome thanks Chamblee, makes sense. Just to continue my understanding, one more question:
- It would never make sense to have a foreign key that is used by two parent keys right?
Best practice would be to have separate primary key for each foreign key. It would be normal for one primary key to be related to one or more foreign keys.
There can, however, be exceptions to that general rule if you use Get (UUID) to generate the primary key values instead of an auto-entered serial number field.
Why would a UUID be different from a Serial number?
I also can't imagine a situation where having the same the foreign key across two parents keys would work properly. If the foreign is always getting their 'number' from the parent key, how would it accommodate more than one parent?
Although, Unless the 'extra' parent key is just coming from an occurrence of the same table, would that be it?
Say you have TableA----<TableB>------TableC
TableA::PrimaryKey = TableB::ForeignKey
TableC::PrimaryKey = TableB::ForeignKey
With an auto-entered serial number, you might have the same value (such as 5) in both the primary key field of TableA and TableC. They would then match to exactly the same record in TableB--which would normally be a problem.
If you use Get ( UUID ), the function would generate unique values across both tables so would not have a record in TableA and TableC with matching values in their respective PrimaryKey fields.
Using UUID is the best way to generate a unique value, as stated by PhilModJunk.
The serial number can contain letters. In the auto-enter calculation, under serial number there is a box that states next value. You could set this value to A1.
You could have one parent key A1....next value would be A2 and so on, then you could have a second parent key B1....next value would B2 and so on.
OK so the auto-enter serial number feature on FM does not create a UUID but just a number that you hope doesn't repeat itself elsewhere in the database? Particularly across tables that have foreign keys in the same table?
If that's the case, how should I go about creating UUIDs instead?
I use auto-entered serial numbers far more often than UUIDs. A primary key only has to be unique for the records in one specific table in order for it to work well in nearly all situations. I find the UUID values to be cumbersome and sometimes the numeric value can be useful in terms of sorting on the ID value.
A text field can be set up with this auto-enter calculation to enter a UUID:
Get ( UUID )