There are times when a unique identifying value exists in the data of a table, as the combination of the values in 2 or more fields. Such a value is a compound key. In 4D, compound keys are implemented indirectly. This Tech Tip describes the theory and implementation of compound keys in 4D.

Often times when creating a table, a single attribute (field) will stand out as the unique identifying value to be used as the primary key. For example, in an Invoices table, the Invoice Number would be the logical candidate to be the primary key, or in a Students table, a Student ID Number would be a logical candidate to be the primary key. However, there are times when a unique identifying value exists in the data of a table, not as a single field, but as the combination of the values in 2 or more fields. Such a value is a compound key. In some database systems, compound keys are directly supported. In 4D, compound keys are implemented indirectly, by concatenating the values from the separate fields into a single field. This field containing the compound key value, can then be used as the primary key for the table.

For example, an invoicing system would have an Invoices table, a Line Items table, and a Products table. The Line Items table would record the assigning of a product to an invoice. Therefore, each Line Items record would have a field in which to enter the invoice number of the invoice to which the line item was assigned, and the product ID of the product being assigned to the invoice. The relations in this system would function as follows:
- A product could have many Line Items, but a Line Item would point back to one product record. So Line Items would be in a relation of many Line Items records to one Product record. Therefore Product ID values in the Line Items table, would not be unique to a single line item record.

- An Invoice could have many Line Items, but a Line Item would point back to one invoice record. So Line Items would be in a relation of many Line Items records to one Invoice record. Therefore, Invoice Number values in the Line Items table would not be unique to a single line item record.

Now, given a business rule that a product is to be placed as a line item once on an invoice (each line item record having a quantity field to record purchases of more than one instance of a product), then the combination of the Invoice Number and the Product ID in a Line Item record would be a value that was unique to that record. So the combination of these two values, this compound key, could function as the primary key attribute for the Line Items table. This functionality could remain theoretical if there was no need to access Line Item records separately from their parent Invoice record, or could be implemented in the Line Items table by concatenating the Invoice Number and Product ID into a third field.