The differences between a primary key vs foreign key

This article explains the fundamental concepts of a primary key vs foreign key, and also explains the difference between a primary key vs foreign key.

What is a Primary Key

The primary key is one column or combination of two or more columns used to identify each record uniquely. The unique characteristics of a primary key vs foreign key are the following:

1. The fields on which the primary key has been created cannot be NULL.
2. A primary key cannot contain more than 32 columns. The limit is specific to oracles only.
3. A primary key can be added to the new table and the existing table. When creating a new table, you can specify the Primary key in a CREATE TABLE If you add a Primary key in the existing table, you can use the ALTER TABLE statement.

The syntax to add a primary key in a new table is following:

create table table_name

(

column_1 datatype,

column_2 datatype,

column_3 datatype,..

constraint "constraint_name” PRIMARY KEY (Key_column_1,Key_column_2,..Key_column_n)

);

In the syntax:

1. constraint_name: Specify the name of the Primary key.
2. Key_column: Specify the individual or list of columns on which you want to create a primary key. The primary key columns must be specified in the bracket. If the primary key contains multiple key columns, each key column must be separated with a comma.

The syntax to add a primary key in an existing table is following:

ALTER TABLE table_name ADD CONSTRAINT constraint_name PRIMARY KEY (key_column_1, key_column_2, … Key_column_n);

In the syntax:

1. constraint_name: Specify the name of the Primary key.
2. column_name: Specify the individual or list of columns on which you want to create a primary key.

Now, let’s see an example.

Example 1: Create a primary key in the new table.

We want to create a table named tblEmployee. The tblEmployee contains a primary key named pk_employee_id on the employee_id column. The syntax to create a table is following:

create table tblEmployee

(

Employee_id number,

Employee_first_Name varchar(500),

Employee_last_Name varchar(500),

Employee_contact number,

CONSTRAINT PK_EMPLOYEE_ID PRIMARY KEY(EMPLOYEE_ID)

);

Example 2: Create a primary key in the new table.

We have created a table named tblCountries. The tblCountries table does not have any primary key, but we want to add a primary key on a country_id column. The syntax to add a primary key is following:

Alter table tblCountriesADD CONSTRAINT pk_country_id PRIMARY KEY (country_id)

Note: If you add a primary key to an existing table, the values within the column of the table must be unique; otherwise, you will encounter an error.

What is a foreign key

The foreign key is used to establish the referential integrity between two tables. The foreign key uses one column or a combination of multiple columns to establish the relationship between two tables. The foreign key on a child table column references the primary key of the parent table. This relationship controls the data being updated or removed from the tables. The syntax to create a foreign key is as follows:

create table table_name

(

column_1 datatype,

column_2 datatype,

column_3 datatype,..

constraint "constraint_name" FOREIGN KEY (key_column_1, key_column_2,.. key_column_n)

REFERENCES Parent_table(parent_column_1,parent_column_2,parent_column_3,….parent_column_n)

)

In the syntax:

1. constraint_name: Specify the name of the foreign key.
2. column_name: Specify the individualor list of columns on which you want to create a foreign key. The list of columns must be specified in brackets.
3. parent_table: Specify the name of the parent table, which is referenced by the foreign key.
4. parent_column: Specify the individual or a list of columns referenced by the foreign key. The list of columns must be specified in brackets.

To maintain the referential integrity between parent and child tables, we can use the following options:

1. DELETE CASCADE: When we use the DELETE CASCADE option, the oracle deletes the record from the child table when a record is deleted from the parent table.
2. DELETE SET NULL: When we use the DELETE CASCADE option, the oracle sets the value of the child table as NULL when a record from the parent table has been deleted.

The DELETE CASCADE and DELETE SET NULL options are specified after the parent table’s columns list.

Now, let’s see some examples of the foreign key.

Example 1: Create a foreign key in new tables

We want to create tables named tblSchool and tblCity. We are creating a foreign key between tblSchool and tblCity. The tblSchool table contains a column named city_id, which references a primary key of tblCity. The primary key column of tblCity is city_id. The table following the script creates a table named tblCity.

create table tblCity

(

City_id number,

City_name varchar(500),

Constraint pk_city_id Primary key (city_id)

);

The following script creates tblSchool with a foreign key named fk_tblschool_school_id.

create table tblSchool

(

School_id number,

school_name varchar(500),

School_Address varchar(1500),

City_ID number,

Country_ID number,

Constraint fk_tblschool_school_id Foreign key (City_id)

REferences tblCity (City_id)

);

Example 2: Create a foreign key in existing tables

In this example, we want to add a foreign key to the country_id column of tblSchool. The country_id column references the country_id column of the tblCountry table. The following script creates a table named tblCountries.

create table tblCountry

(

country_id number,

country_name varchar(500),

Constraint pk_tblCountry_country_id Primary key (country_id)

)

The following query adds a foreign key named FK_tblcountry_country_id.

Alter table tblSchool add constraint FK_tblcountry_country_id foreign Key(country_id)

REferences tblCountry (country_id);

Differences between a primary key vs foreign key

Now, let us take a look at some basic differences between a primary key vs foreign key.

Summary

In this article, we learned about the foundational primary key vs foreign key. The following topics are covered in this article:

1. An overview of the Primary key with examples.
2. An overview of the foreign key with the example.
3. How primary key and foreign key are different from each other.

Related Links

Blog: How to add Parent Table Name to FK columns at once 

Blog: How to use Toad® developer tools to migrate to an open source database – 2 

Blog: SQL tools – 8 ways you can’t live (or work) without them 

Blog: Best developer tool: An Oracle Ace's favorite things in Toad for Oracle Base 

Blog: Dark mode has arrived for Toad for Oracle 15.1 

Have questions, comments? 

Head over to the Toad for Oracle forum on Toad World®!  Chat with Toad developers, and lots of experienced users. 

Help your colleagues

If you think your colleagues would benefit from this blog, share it now on social media with the buttons located at the top of this blog post.