What is the difference between a composite key, primary key and foreign key?
A primary key is defined as a key or database column which uniquely identifies each row in a database table. A composite key is a set of more than one key that, together, uniquely identifies each record.
A foreign key, on the other hand, is a key in some table which uniquely identifies rows in another table – or, in other words, a key that tracks to a primary key in another table.
An example would be a list of homes on a real estate market. In a well-ordered database, there should be a primary key that uniquely identifies each record. How this works may have to do with the sophistication of the database.
In some cases, the homes may only be uniquely identified by a mortgage number – all other data (towns, streets, house numbers) is not unique to each record. The mortgage number would be the primary key. Suppose, however, that an MLS realtor’s listing technology assigns its own unique numbers to the records in the table. Then, there will be two keys that developers might identify as “candidate keys”: the mortgage number, and the MLS number. One of them will qualify as the “primary key” in what some would consider an arbitrary way.
A composite key, then, would be the combination of two keys: for example, the combination of house number and street might qualify as a composite key, given that the market listings are local. If so, then when someone searches using both the house number and the street, they should only get one single record returned.
Meanwhile, if there is a key in a linked table, for example, a buyer’s table, that references the primary key, that will be a foreign key.