SQL Primary Key

The primary key is a column or multiple columns which uniquely identify the rows in the table.

Primary key constraint is to be defined when you want to create a primary key

Most commonly only single column will be used as primary key. If multiple columns are used as the primary key then it is called as composite primary key.

Usage of more columns as primary key uses more data storage space which decreases the performance.

 

There are some important points that need to be followed when creating a primary key:

  • The primary key always has unique data.
  • The primary key cannot have null values.
  • The primary key cannot have a duplicate value.
  • A table can contain only one primary key.
  • A primary key length cannot exceed 900 bytes.

 

The main advantage of creating the primary key is to get fast access.

Note: - In Oracle, the primary key cannot contain more than 32 columns.

 

How to create Primary Key?

The following command is used to create a PRIMARY KEY.

MySQL: -

CREATE TABLEtable_name
(
column1 NOT NULL,
column2 Constraint,
PRIMARY KEY (column1)
)

 

For SQL Server, Oracle, and MS Access: -

CREATE TABLEtable_name
(
column1 constraintsPRIMARY KEY,
column2,...
)

 

Example:

Let us take an example where we create a primary key for a table

CREATE TABLE Students
(
ID NOT NULL,
Name varchar(255),
Address varchar(255),
City varchar(255),
PRIMARY KEY (ID)
)

 

For SQL Server, Oracle, MS Access:

CREATE TABLE Students
(
ID NOT NULLPRIMARY KEY,
Name varchar(255),
Address varchar(255),
City varchar(255),
)

 

We can also create primary key for multiple columns the command is written as follows:

CREATE TABLE Students
(
ID NOT NULL,
Name varchar(255) NOT NULL,
Address varchar(255),
City varchar(255),
CONSTRAINTStudent_IDPRIMARY KEY (ID, Name)
)

 

Note: In the above command there is one Primary key Student_ID. However, it is made of two columns ID+Name.

 

SQL Primary key on ALTER Table

To create a primary key on the table that is already created then the following command is to be used:

On One column

ALTER TABLEtable_name
ADD PRIMARY KEY (column_name)

 

On Multiple columns

ALTER TABLEtable_name
ADD CONSTRAINTprimarykey_namePRIMARY KEY (column1, column2)

 

Example:

The command can be written on Students table as

On One column

ALTER TABLE Students
ADD PRIMARY KEY (ID)

 

On Multiple columns

ALTER TABLE Students
ADD CONSTRAINTStudent_IDPRIMARY KEY (ID, Name)

 

How to Drop a Primary key

If you want to drop a primary key constraint you should use the following syntax command:

MySQL:

ALTER TABLEtable_name
DROP PRIMARY KEY

 

SQL Server / Oracle / MS Access:

ALTER TABLEtable_name
DROP CONSTRAINTprimarykey_name