Difference Between Primary Key And Foreign Key With Examples

What is a Primary Key?

A primary key is a special relational database table column (or combination of columns) designated to uniquely identify each table record. It is a unique identifier, such as a driver license number, telephone number (including area code), or vehicle identification number (VIN). A relational database must always have one and only one primary key. Primary keys typically appear as columns in relational database tables.

A primary key’s main features are:

• It must contain a unique value for each row of data.
• It cannot contain null values.
• Every row must have a primary key value.

A primary key might use one or more fields already present in the underlying data model, or a specific extra field can be created to be the primary key.

Example

For example in a commercial bank, a database must hold all of the data stored by a commercial bank. Two of the database tables include the CUSTOMER_MASTER, which stores basic and static customer data (name, date of birth, address, Social Security number, etc.) and the ACCOUNTS_MASTER, which stores various bank account data (account creation date, account type, withdrawal limits or corresponding account information, etc.).

To uniquely identify customers, a column or combination of columns is selected to guarantee that two customers never have the same unique value. Thus, certain columns are immediately eliminated, e.g., surname and date of birth.

A good primary key candidate is the column that is designated to hold Social Security numbers. However, some account holders may not have Social Security numbers, so this column’s candidacy is eliminated.

The next logical option is to use a combination of columns, such as adding the surname to the date of birth to the email address, resulting in a long and cumbersome primary key.

The best option is to create a separate primary key in a new column named CUSTOMER_ID. Then, the database automatically generates a unique number each time a customer is added, guaranteeing unique identification.

The choice of a primary key in a relational database often depends on the preference of the administrator. It is possible to change the primary key for a given database when the specific needs of the users changes. For example, the people in a town might be uniquely identified according to their driver license numbers in one application, but in another situation it might be more convenient to identify them according to their telephone numbers.

What You Need To Know About Primary Key 

• A Primary Key constrain is a column that uniquely identifies every row in the table of the relational database management.
• It is used to identify each record into the database table uniquely.
• The primary key is a clustered index and data in the DBMS table are physically organized in the sequence of the clustered index.
• Primary Key never accepts null values.
• You can have a single primary key in a table.
• The value of the primary key can’t be removed from the parent table.
• A table can have only one primary key.
• The primary key constraint can be defined on the temporary tables.
• No two rows can have any identical values for a primary key.
• It cannot create a parent-child relationship in a table.
• Primary is a unique key attribute, it cannot store duplicate values in relation.
• Under primary keys, a value can be deleted from the referencing table by making sure that the value is not present in the foreign key reference table.

What is a Foreign Key?

A foreign key is a column or group of columns in a relational database table that provides a link between data in two tables. It acts as a cross-reference between tables because it references the primary key of another table, thereby establishing a link between them.

The majority of tables in a relational database system adhere to the foreign key concept. In complex databases and data warehouses, data in a domain must be added across multiple tables, thus maintaining a relationship between them. The concept of referential integrity is derived from foreign key theory.

While a primary key may exist on its own, a foreign key must always reference to a primary key somewhere. The original table containing the primary key is the parent table (also known as referenced table). This key can be referenced by multiple foreign keys from other tables, known as “child” tables.

For any column acting as a foreign key, a corresponding value should exist in the linked table. Special care must be taken while inserting data and removing data from the foreign key column, as a careless deletion or insertion might destroy the relationship between the two tables.

In simpler words, a foreign key is a set of attributes that references a candidate key. For example, a table called TEAM may have an attribute, MEMBER_NAME, which is a foreign key referencing a candidate key, PERSON_NAME, in the PERSON table. Since MEMBER_NAME is a foreign key, any value existing as the name of a member in TEAM must also exist as a person’s name in the PERSON table; in other words, every member of a TEAM is also a PERSON.

What You Need to know About Foreign Key

• A Foreign key is a column or group of columns in a relational database table that provide a link between data in two tables.
• It is used to link two tables together.
• A foreign Key cannot automatically create an index, clustered or non-clustered.
• A foreign key may accept multiple null values.
• You can have multiple foreign keys in a table.  
• The value of foreign key value can be removed from the child table.
• A table can have more than one foreign key.
• A foreign key constraint cannot be defined on the local or global temporary tables.
• A foreign key can contain duplicate values.
• It can create parent-child relationship in a table.
• Duplicate values can be stored in the foreign key column.
• Under foreign keys, the values can be deleted without any glitches since it does not matter whether the value is still present in the primary keys reference table or not.

Difference Between Primary Key And Foreign Key In Tabular Form