Primary Key in SQL: Syntax, Usages, and Example

Introduction

A key with one or more columns that will aid in uniquely identifying each tuple or record in a table is represented by a primary key constraint.

Qualities

Duplicate values are prohibited; that is, only unique values should be assigned to the column designated as the main key.

There aren't any NULL values in the primary key column. Therefore, the column containing the primary key has a mandatory value.

Although a primary key may include more than one column, there is only one primary key per table.

It is not possible to add a new row using the primary key that already exists.

categorized as,

1. A single-column, basic primary key.
2. Multiple columns make up the composite primary key.

As stated in the Make a Table or Modify a table statement.

A PRIMARY KEY constraint can be used to create the primary key in a table.

It can be created at a couple of levels.

1. Column
2. Table.

SQL Primary Key at Column level

If the primary key contains only one column, it should be defined at the column level. The following code creates the primary key "primary_field_name" on the table.

Syntax

Field_name data_type PRIMARY KEY

Example

CREATE TABLE Employee
(
	Id INT NOT NULL PRIMARY KEY,
	[Name] VARCHAR(50) NOT NULL,
	City VARCHAR(20) NOT NULL
)

Let’s verify the primary key with an example.

INSERT INTO Employee 
VALUES (1, 'Jaimin Shethiya', 'Vadodara');

Let’s try the execution of the same query again in SQL.

It will throw an exception.

Let’s try with the NULL inserted as the primary key value.

INSERT INTO Employee 
VALUES (NULL , 'Jaimin Shethiya', 'Vadodara');

It will throw an error due to null not being allowed in the primary key.

SQL Primary key at Table level

Whenever the primary key contains more than one column, it has to be specified at the table level.

Syntax

CREATE TABLE table_name
(
	field1 data_type,
	field2 data_type,
	...
	PRIMARY KEY (field1)
)

Example

CREATE TABLE Employee
(
	Id INT NOT NULL,
	[Name] VARCHAR(50) NOT NULL,
	City VARCHAR(20) NOT NULL,
	PRIMARY KEY (Id)
)

Primary Key with Multiple Columns

If you want to add a primary key to more than one column, then that is not possible at the column level; that is possible at the table level.

CREATE TABLE Employee
(
	Id INT NOT NULL,
	[Name] VARCHAR(50) NOT NULL,
	City VARCHAR(20) NOT NULL,
	PRIMARY KEY (Id, [Name])
)

Primary Key with Alter Table

The primary key is typically defined at the time the table is created, but occasionally it might not be created in the table that already exists. However, we can use the Alter Statement to add the primary key.

For a Single Column

Syntax

ALTER TABLE Table_name
ADD PRIMARY KEY (column1);

Example

ALTER TABLE Employee
ADD PRIMARY KEY (Id);

For Multiple Columns

Syntax

ALTER TABLE Table_name
ADD CONSTRAINT primary_key_name PRIMARY KEY (column1, column2);

Example

ALTER TABLE Employee
ADD CONSTRAINT Employee_PK PRIMARY KEY (Id, [Name]);

Remove Primary Key

We can remove the primary key constraint from a table using the DROP statement.

Syntax

ALTER TABLE table_name
DROP CONSTRAINT primary_key_name;

Example

ALTER TABLE Employee
DROP CONSTRAINT Employee_PK;

We learned the new technique and evolved together.

Happy coding!