Database keys

There are three types of keys in the database which are Primary Key, Foreign Key, and Composite Key. A primary key is a column or a set of columns that uniquely identify a row in a table. A foreign key is a column or set of columns in a table that match the primary key of another table. A composite key is a combination of two or more columns in a table that can uniquely identify a row in a table.

Primary key

A primary key is a special type of column that uniquely identifies each row in a table. A primary key column cannot have NULL values. Most tables have a primary key, which can consist of one or more columns.

There are four main types of keys in databases:

Primary keys
Foreign keys
Candidate keys
Superkeys

Foreign key

In database terms, a foreign key is a column or set of columns in a relational table that provides a link between data in two tables. The foreign key can be used to cross-reference tables, but it also imposes certain constraints on the data. For example, a foreign key can be used to ensure that every record in one table has a corresponding record in another table.

Composite key

A composite key is a key that consists of two or more attributes (columns) that uniquely identify an entity in a database table. In other words, it is a combination of two or more attributes (columns) that can uniquely identify a record without referring to any other data.

For example, consider a table that stores information about students enrolled in a course. The table has three columns: StudentID, CourseID, and EnrollmentDate. In this table, the StudentID and CourseID columns together form a composite key because they uniquely identify each row in the table. That is, no two rows in the table can have the same StudentID and CourseID values.

Super key

A superkey is a set of attributes within a database table that can uniquely identify a tuple (row) within that table. A superkey can therefore be thought of as a subset of the attributes that make up the primary key for a given table. In other words, every superkey is also a candidate key, but not every candidate key is a superkey.

Candidate key

A Candidate key is a column or a set of columns that can uniquely identify a row in a table. A table can have more than one candidate key, out of which one candidate key is selected as the Primary key.

Surrogate key

A surrogate key is a unique identifier for either an entity in the real world or an object in a database. A surrogate key is a stand-in key: it uniquely identifies something, but has no inherent meaning.

In databases, surrogate keys are typically used to identify records when the actual business keys are not suitable. For example, a customer table might have a column for customer ID. That ID could be the actual business key (e.g., a government issued ID number) or it could be a surrogate key generated by the database (e.g., 1, 2, 3). In the latter case, the surrogate key has no inherent meaning – it is just used to uniquely identify each customer row.

There are several reasons why you might want to use a surrogate key rather than a business key:

• The business keys might not be unique. For example, two customers might have the same government issued ID number.
• The business keys might not be constant. For example, a customer might change their name, which would then change their government issued ID number.
• The business keys might not be known in advance. For example, when you’re building a database of historical data, the IDs of future records (e.g., death certificates) are not known in advance.
• The business keys might be sensitive information that should not be exposed (e.g.,Social Security Number).
  Natural key
  
  A natural key is a key that consists of attributes that naturally identify an entity as uniquely different from all other entities. In other words, the attribute or combination of attributes that forms the key is not man-made, but rather, exists in nature. For example, a social security number is a natural key because it uniquely identifies a person and no two people can have the same social security number.

A natural key can be formed by a single attribute or by multiple attributes. When multiple attributes are used to form a natural key, they are called composite attributes. For example, a driver’s license number is typically composed of multiple attributes: the license holder’s last name, first name, date of birth, and possibly middle initial.