SQL Primary Key

Filed Under: SQL

In a world driven with data all over, it is very easy to get duplicate data. The nightmare of any database table designer is to create a table with the possibility of duplicate data insertion. To overcome the problem of duplicate rows, SQL primary key is used to uniquely identify each row.

SQL Primary Key

A Primary key is a column or set of columns that can uniquely identify a row. When we create a table we should specify the primary key. Also, primary key creation comes with a set of rules mentioned below.

  1. A primary key can not be null.
  2. A primary key must always contain unique values. If its a combination of multiple columns that the combination must be unique.
  3. One table can have only one primary key.

Let us now try to understand primary key creation for the below mentioned databases.

  1. MySQL
  2. PostgreSQL
  3. SQL Server

MySQL Primary Key

Syntax: –

CREATE TABLE <table_name>(<column_name1> <data_type> NOT NULL,
<column_name2> <data_type> NOT NULL,
<column_name3> <data_type> NOT NULL,
PRIMARY KEY (column_name1));

In the syntax above, a PRIMARY KEY keyword is used to specify that the column will be used a the primary key for the table.

Let’s create a customer table to understand this better.

CREATE TABLE customer
(cust_id varchar(8) NOT NULL UNIQUE, 
cust_name varchar(50) NOT NULL, 
state varchar(25), 
country varchar(25),
PRIMARY KEY (cust_id) );

Post-execution of the above query at MySQL workbench. The following screen will appear.

On performing a table inspection following screen will appear.

Primary Key Column

Primary Key Column

The value for Unique column is “YES” which mean that the column will accept a unique value. Also, the key column has row value “PRIMARY” and the row value for columns column specifies the column that will be the primary key.

PostgreSQL Primary Key

Syntax: –

CREATE TABLE <table_name>(<column_name1> <data_type> NOT NULL,
<column_name2> <data_type> NOT NULL,
<column_name3> <data_type> NOT NULL,
PRIMARY KEY (column_name(n)));

In the syntax above, a PRIMARY KEY keyword is used to specify that the column or set of columns will be used a the primary key for the table.

Let’s create an Employee table to understand this better.

CREATE TABLE Employee(
employee_no integer PRIMARY KEY,
employee_name character(50),
employee_city character(35),
employee_phn numeric);

In the table above, we are creating primary key using just one column “employee_no”. Following is the query that should be used for the creation of a table with multiple columns.

CREATE TABLE Employee(
employee_no integer,
employee_name character(50),
employee_city character(35),
employee_phn numeric,PRIMARY KEY (employee_no,employee_phn));

In the table above, we are creating primary key using two columns “employee_no and employee_phn”.

Post-execution of the above query at PGAdmin. The following screen will appear.

On checking the properties of the table.

Primary Key Column PostgreSQL

Primary Key Column PostgreSQL

SQL Server Primary Key

Syntax: –

CREATE TABLE <table_name>(<column_name1> <data_type> NOT NULL,
<column_name2> <data_type> NOT NULL,
<column_name3> <data_type> NOT NULL,
PRIMARY KEY (column_name(n)));

In the syntax above, a PRIMARY KEY keyword is used to specify that the column or set of columns will be used a the primary key for the table.

Let’s create an Employee table to understand this better.

CREATE TABLE Employee(
employee_no integer PRIMARY KEY,
employee_name character(50),
employee_city character(35),
employee_phn numeric);

Post-execution of the above query at SQL Server Management Studio. The following screen will appear.

On expanding the table structure.

Primary Key Column SQLServer

Primary Key Column SQLServer

Summary

The primary key is the most important aspect of any database table. When you design the tables, use primary keys wisely. Make sure to understand the data that will be inserted in the table and then find the unique value to assign the primary key. If there are no unique values, you can add an extra column with auto-increment values for the primary key.

Leave a Reply

Your email address will not be published. Required fields are marked *

close
Generic selectors
Exact matches only
Search in title
Search in content
Search in posts
Search in pages