In a database management system (DBMS), keys help to uniquely identify rows of records in a table that may contain duplicated records too. Besides that, keys help to manage relationships between tables. There are many different keys in the DBMS, and each of them has its own meaning. Let’s explore them with some picture illustrations that I get from the Internet to help us understand and differentiate the keys.
It is an attribute (key) or a set of attributes (keys) that can be used to identify rows of data in a table is called Super Key. A super key can have additional attributes that are not needed for unique identification.
A minimal subset of a super key which can be used to uniquely identify rows of data in a table. Candidate Key is a super key with no repeated attributes. According to the Educa website, there are few rules that need to follow when it comes to the selection of a candidate key:
- A Candidate Key should comprise of distinctive values.
- A Candidate Key can have various attributes.
- A Candidate Key cannot comprise of null values.
- A Candidate Key must uniquely identify each row in the table.
Then, the Primary Key is carefully chosen from the given Candidate keys.
A primary key is a column or group of columns in a table that uniquely identify every row in that table. The Primary Key cannot be a duplicate. This means the same value cannot appear more than once in the table. A table cannot have more than one primary key. It is also known as Entity Integrity Rule.
Rules for defining Primary key:
- Two rows cannot have the same primary key value.
- It must for every row to have a primary key value.
- The primary key field cannot be null.
- The value in a primary key column can never be modified or updated if any foreign key refers to that primary key.
The alternate key is a column or group of columns in a table that uniquely identify every row in that table. A table can have multiple choices for a primary key but only one key can be set as the primary key. All the keys which are not primary key are called an Alternate Key.
The illustration below is taken from guru.99.com that clearly differentiate the candidate key, primary key and alternate key from a table that has identified that StudID, Roll_No, and Email are the candidate keys, however, the StudID is chose to be the primary key mainly because it is a unique running number that can be used to identify a student in the table. Then, Roll_No and email are the alternate keys for this table.
The foreign key is a column that creates a relationship between two tables. It is added into a table to establish this relationship. It acts as a cross-reference between two tables as it references the primary key of another table. The purpose of Foreign keys is to maintain data integrity and allow navigation between two different instances of an entity. It is also known as Referential Integrity.
The differences between primary key and foreign key
- Primary key helps to uniquely identify a record in the table while foreign key is a field in the table that is the primary key of another table.
- Primary key cannot be null values and a foreign key may accept multiple null values.
- Primary key is a clustered index and data in the DBMS table are physically organized in the sequence of the clustered index. A foreign key cannot automatically create an index, clustered or non-clustered. However, you can manually create an index on the foreign key.
- Can have the single Primary key in a table but can have multiple foreign keys in a table.
A composite key is a combination of two or more columns that uniquely identify rows in a table. The combination of columns guarantees uniqueness, though individually uniqueness is not guaranteed. Hence, they are combined to uniquely identify records in a table.
A compound key has two or more attributes that allow you to uniquely recognize a specific record. Another word, it is a primary key that does not consist of a single column but two or more columns that uniquely identify a row. It is possible that each column may not be unique by itself within the database. However, when combined with the other column or columns the combination of composite keys become unique. The purpose of the compound key is to uniquely identify each record in the table.
The differences between composite key and compound key
The difference between compound and the composite key is that any part of the compound key can be a foreign key, but the composite key may or maybe not a part of the foreign key.
An artificial key which aims to uniquely identify each record is called a surrogate key. These kind of key are unique because they are created when you don’t have any natural primary key. They do not provide any relation to the table data in the table. Surrogate key is usually an ordered integer.
- Entity Integrity Rules.
- Referential Integrity Rules.
- Business Logic Integrity.
Integrity (validation) pertaining to the business logic, for example, the postal code shall be 6-digit within a certain range.