Primary Key vs. Foreign Key

Primary Key:-

In the relational model, a Primary Key (PK) is used to designate a unique identifier for each row when you design a database. A Primary Key can be composed of one or more columns. In the example below, the Primary Key is the employee number.

Primary Key Rules:-

Rules governing how Primary Keys must be defined and how they function are:

Rule 1: Only one Primary Key per table.
Rule 2: A Primary Key value must be unique.
Rule 3: The Primary Key value cannot be NULL.
Rule 4: The Primary Key value should not be changed.
Rule 5: The Primary Key column should not be changed.
Rule 6: A Primary Key may be any number of columns.

Rule 1: One Primary Key:-

Each table must have one, and only one, Primary Key. In any given row, the value of the Primary Key uniquely identifies the row. The Primary Key may span more than one column, but even then, there is only one Primary Key.

Rule 2: Unique PK:-

Within the column(s) designated as the Primary Key, the values in each row must be unique. No duplicate values are allowed. The Primary Key's purpose is to uniquely identify a row. In a multi-column Primary Key, the combined value of the columns must be unique, even if an individual column in the Primary Key has duplicate values.

Rule 3: PK Cannot Be NULL:-

Within the Primary Key column, each row must have a Primary Key value and cannot be NULL (without a value). Because NULL is indeterminate, it cannot "identify" anything.

Rule 4: PK Value Should Not Change:-

Primary Key values should not be changed. If you changed a Primary Key, you would lose all historical tracking of that row.

Rule 5: PK Column Should Not Change:-

Additionally, the column(s) designated as the Primary Key should not be changed. If you changed a Primary Key, you would lose all the information relating that table to other tables.

Rule 6: No Column Limit:-

In the relational model, there is no limit to the number of columns that can be designated as the Primary Key, so it may consist of one or more columns. In the example below, the Primary Key consists of three columns: EMPLOYEE NUMBER, LAST NAME, and FIRST NAME.

Foreign Key:-

A Foreign Key (FK) is an identifier that links related tables. A Foreign Key defines how two tables are related to each other. Each Foreign Key references a matching Primary Key in another table in the database. For example, in the table below, the Department Number column that is a Foreign Key actually exists in another table as a Primary Key.

Foreign Key Rules:-

Rules governing how Foreign Keys must be defined and how they operate are:

Rule 1: Foreign Keys are optional.
Rule 2: A Foreign Key value may be non-unique.
Rule 3: The Foreign Key value may be NULL.
Rule 4: The Foreign Key value may be changed.
Rule 5: The Foreign Key column may be changed.
Rule 6: A Foreign Key may be any number of columns.
Rule 7: Each Foreign Key must exist as a Primary Key in the related table.

Rule 1: Optional FKs:-

Foreign Keys are optional; not all tables have them. Tables that do have them can have multiple Foreign Keys because a table can relate to multiple other tables. In the example table below:

• The Department Number Foreign Key relates to the Department Number Primary Key in the Department Table elsewhere in the database.
• The Job Code FK relates to the Job Code PK in the Job Code Table, elsewhere in the database.

Rule 2: Unique or Non-Unique FKs:-

Duplicate Foreign Key values are allowed. More than one employee could be assigned to the same department.

Rule 3: FKs Can Be NULL:-

NULL (missing) Foreign Key values are allowed. For example, under special circumstances, an employee might not be assigned to a department.

Rule 4: FK Value Can Change:-

Foreign Key values may be changed. For example, if Arnando Villegas moves from Department 403 to Department 587, the Foreign Key value in his row would change.

Rule 5: FK Column Can Change (in Certain Circumstances):-

You may add additional Foreign Key columns as needed. If you change an existing Foreign Key column, however, you may lose the relationship information between that table and the table containing the Primary Key.

Rule 6: FK Has No Column Limit:-

The Foreign Key may consist of one or more columns. A multi-column foreign key is used to relate to a multi-column Primary Key in the related table. In the relational model, there is no limit to the number of columns that can be designated as a Foreign Key.

Rule 7: FK Must Be PK in Related Table:-

Each Foreign Key must exist as a Primary Key in the related table. A department number that does not exist in the Department Table would be invalid as a Foreign Key value in the Employee Table.

This rule can apply even if the Foreign Key is NULL, or missing. Remember, a missing value is defined as a non-value; there is no value present. So the rule could be better stated: if a value exists in the Foreign Key column, it must match a Primary Key value in the related table.

Related Posts