Compatibility: Version 6.5.x and 6.7.x

What is the primary key, and how is it implemented in 4D?

The rules for proper relational table design state that each table must store a value that is unique for every record. This unique value serves as an identifier for the record.

Let's say that you work for a video rental company, and a customer calls with a question concerning an invoice. You might search your invoices looking for invoices that have that customer's company name. Now, hopefully they have rented videos from you more than once, in which case your search would find more than one invoice. So, you might ask them the date of the invoice, and if they remembered it, you might search for their invoice by company name and date. But this could also find more than one invoice.

To solve this problem, each invoice is assigned an invoice number that is used to reference a specific invoice, and when a customer calls, they refer to the invoice by invoice number. This identifying number has two notable characteristics:

1. Since the number is used to reference a specific invoice record, no two invoice records can have the same number. The number must be unique for each invoice.
   
   
2. The field in which the identifying number is stored must have a value in each invoice record; it cannot be null, otherwise how would you refer to a specific invoice if it had no value in the invoice number field?
   
   

These facts were true of paper-based systems, before computerization, and remain true in computerized database systems. Of course, in such a system one might also keep track of other things such as customers and videos. In such a system, each kind of data is stored in it's own table, and each table would need a unique identifying value for its records. So, every customer record would be assigned a customer number, and every video record would be assigned a video ID number. Each of these numbers would have to be unique and would have to have a value. So we could summarize by saying that in tables designed for a relational database system:

• Each table has a field that stores an identifying value for each record.
  
  
• The identifying value must be unique for every record and this identifying value is mandatory for every record.
  
  

The field that holds this unique mandatory value for each record is known as the primary key for that table. In 4D, we enforce the unique attribute and the mandatory attribute of the primary key field by setting these attributes in the Field Properties dialog.