SQL Primary Key: The Complete Guide

0
17
SQL PRIMARY KEY Tutorial With Example | Primary Key in SQL

SQL primary key is a field in a table that uniquely identifies a row in a table. If the column has a primary key constraint, then it will contain unique values and will not be able to contain any NULL values.

A primary key length cannot exceed more than 900 bytes.

SQL PRIMARY KEY

SQL PRIMARY KEY constraint uniquely identifies each row in the table. Primary keys must contain UNIQUE values and cannot contain the NULL values. The table can have only ONE primary key; and in the table, that primary key can consist of single or multiple columns (fields).

A table will contain only one primary key, which may consist of single or multiple fields, and when multiple fields are used as a primary key, it is known as composite keys.

If the table has a primary key defined in any field, it cannot have two or more records with the same values.

SQL Composite Key

When the multiple fields are used as the Primary Key, they are called a composite key.

Key Points for Primary Key

  1. The primary key enforces the entity integrity of the table.
  2. The primary key always has unique data.
  3. The primary key length cannot be exceeded 900 bytes.
  4. The primary key cannot have a null value.
  5. There must be no duplicate value for a primary key.
  6. The table can contain only one primary key constraint.

If a table has the primary key defined on any field(s), then you cannot have the two records having the same value in that field(s). There are the following ways to create a primary key:

  1. Using create statement.
  2. Using an alter statement.

Create Primary Key Using CREATE TABLE

See the following syntax.

Adding a primary key after the name of the column.

Create table table_name
( 
column1 datatype [NULL|NOT NULL], [PRIMARY KEY],
column2 datatype [NULL|NOT NULL], [PRIMARY KEY],
…………………….
Column(n) datatype [NULL|NOT NULL]);

Using Constraint names

CREATE TABLE table_name
(
Column1 datatype [NULL|NOT NULL],
Column2 datatype [NULL|NOT NULL],
………….
CONSTRAINT constraint_name PRIMARY KEY (column1, column2, …., column(n));

NOTE

[NULL|NOT NULL] is optional. If you don’t mention any of these, then by default, a column can take NULL values.

LET’S CLEAR THE SYNTAXES USING AN EXAMPLE

Create table employees
(emp_id integer, name varchar(40), salary integer);

Now, after executing this command, a table named employees with emp_id as a primary key will be created. After the creation of the table, writes.

Desc employees;

You can see that the emp_id key field will have a text known as PRI, known as the PRIMARY KEY. See the following output.

SQL PRIMARY KEY Tutorial

Now, see the following query.

create table employees
(emp_id integer, name varchar(40), salary integer, 
CONSTRAINT employees_id 
PRIMARY KEY(emp_id));

Now, after executing this command, a table named employees with emp_id as the primary key will be created.

After the creation of the table, write the following query.

Desc employees;

You can see that the emp_id key field will have a text known as PRI, known as the PRIMARY KEY. See the following output.

Using Constraint names

Let’s create a composite key, i.e. a primary key having more than one column. See the following query.

create table employees
(emp_id integer, name varchar(40), salary integer, 
CONSTRAINT employees_id PRIMARY KEY(name, salary));

Now, after executing this command, a table will be created named employees having name and salary as the primary key.

After the creation of the table, write the following query.

Desc employees;

You can see that the name and salary key field will have a text known as PRI, known as the PRIMARY KEY. See the following output.

SQL PRIMARY KEY Example

Create PRIMARY KEY Using Alter Statement

See the following syntax.

Alter table table_name ADD primary key(column name);

See the following query.

Alter table employees add primary key(emp_id);

See the following output.

Create PRIMARY KEY Using Alter Statement

Explanation

You can see that at the time of the table creation, we had not mentioned any column as the primary key. So, to make a column unique, we used an alter statement.

Create COMPOSITE KEY Using Alter Statement

See the following syntax.

Alter table table_name 
ADD constraint 
constraint_name primary key(column(s));

See the following query.

alter table employees 
add constraint employees_pri Primary key(Name, salary);

See the output.

Create COMPOSITE KEY Using Alter Statement

Explanation

You can see that at the time of the table creation, we had not mentioned any column as a primary key. So, to make a column(s) unique, we used an altered statement.

DELETE A PRIMARY KEY

See the following syntax.

Alter table table_name 
DROP primary key;

Let’s create the table employees with the primary key as mentioned above. Then we will delete it.

See the query.

Alter table employees drop primary key;

See the following output.

DELETE A PRIMARY KEY

Disable Primary Key

You can disable the primary key using the ALTER TABLE statement in SQL Server (Transact-SQL).

The following is the syntax to disable the Primary Key using the ALTER INDEX statement in SQL Server (Transact-SQL).

ALTER INDEX constraint_name ON table_name
DISABLE;

Let’s disabling a primary using the ALTER INDEX statement in SQL Server (Transact-SQL).

For example:

ALTER INDEX employees_pk ON employees
DISABLE;

#Enable Primary Key

You can enable the Primary Key using the ALTER INDEX statement in SQL Server (Transact-SQL). See the following syntax.

ALTER INDEX constraint_name ON table_name
REBUILD;

Let’s look at enabling a primary key using the ALTER INDEX statement in SQL Server.

ALTER INDEX employees_pk ON employees
REBUILD;

That’s it for this tutorial.

Leave A Reply

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.