I believe *every* table should have a PRIMARY KEY (number, serial, text, anything that is UNIQUE to that table). However when relating two tables (even one-to-one relationships) you don't use the PRIMARY KEY = PRIMARY KEY between the two tables. You have a PRIMARY KEY in the first table = FOREIGN KEY in the second table.
If you need, create another field which is NOT the primary key and you may then use it to match tableOne::myField = tableTwo::myField.
But the Auto-enter in tableOne can be the PRIMARY KEY and match the FOREIGN KEY in tableTwo (not auto-enter serial).
- Make a trigger to create the record in the second table when the record in the first table is committed (pass the key in the script parameter)
- or Set a field (based on the relationship) by script that will automatically set the Primary Key (auto enter) and the Foreign Key in tableTwo (if the relationship allows creation of related records).
An analogy for your transaction-log table relationship is company-contacts in a CRM application.. For example company Acme, Inc could have one or more contacts e.g Bill in Accounting, Sam in Vendor Relations and so on. The company IDs are distinct from the contact IDs. Both sets of IDs can (and should) be auto-generated. As Beverly points out, you need to link the two tables on a common field. In my CRM example, that would be Customer ID. You can do this in Relationships in 'Manage Database' in FileMaker.