I want to briefly go over some terms. I think it’s important to get these terms straight. It will make things easier in the future. These terms can be confusing and are often thrown around. So I wanted to define them now.
Some definitions:
Primary Key – A primary key is a special column or a combination of columns in a database. The primary key is used to uniquely identify all the records in the table. A primary key has two main features. It must contain a unique value for each row in the table, and it cannot contain null values. A primary key usually is an existing column or can be a column that is generated for a specific table.
Foreign Key – A foreign key is a column of combination of columns in a database that is a used as a link between two tables in a database. The foreign key is a primary key for the linked table. Therefore it is used as the link between the two tables as it can identify rows in the linked table. A foreign key is not usually the primary key in the table.
Composite Key – A composite key is a key that is a combination two or more columns. If the columns are taken separately, uniqueness of the records is not certain. Uniqueness is only certain when the columns are used in conjunction.
Candidate Key – A candidate key is a column or a combination of columns in a table that can uniquely identify all the records in a table. A primary key is a special kind of candidate key. All primary keys are candidate keys. Not all candidate keys are primary keys. Generally the “best” candidate key becomes a primary key. A candidate key can be a composite key.
Indexing – Indexes are used to query databases really quickly. However, it can be taxing on the computer system. So you only want to create indexes on columns that will be frequently searched against. Primary keys are automatically indexed in databases.
Comparing Foreign and Primary Keys:
- Be one or more columns: Both
- Duplicate Values: Only foreign keys can have duplicate values.
- Null Values: Only foreign keys can contain nulls.
- Uniquely identify a row: Required for primary keys. Not required for foreign keys, but can occur.
- Number allowed per table: A table can have only one primary key. There can be multiple foreign keys in a table.
- Indexed: Primary key is automatically indexed. Foreign keys are not automatically indexed.
