AppDividend
Latest Code Tutorials

SQL PRIMARY KEY Tutorial With Example | Primary Key in SQL

0

SQL PRIMARY KEY Tutorial With Example | Primary Key in SQL is today’s topic. A primary key is a field in a table which is used for uniquely identifying a row in a table. If a column has a primary key constraint, then it will contain unique values and will not able to contain any NULL values. A primary key length cannot exceed more than 900 bytes.

SQL PRIMARY KEY

A 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 the 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, then it is known as composite keys.

If the table has a primary key defined in any of the fields, then it cannot have two or more records having 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. Primary key enforces an entity integrity of the table.
  2. The primary key always has a unique data.
  3. The primary key length cannot be exceeded than 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 of 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 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 will be created named employees having emp_id as a primary key. 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, which is 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 will be created named employees having emp_id as the primary key.

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, which is 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 name and salary key field will have a text known as PRI, which is 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 creation of the table, we had not mentioned any column as the primary key. So, to make a column unique, we had used 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 creation of the table, we had not mentioned any column as a primary key. So, to make column(s) unique, we had used alter statement.

#DELETE A PRIMARY KEY

See the following syntax.

Alter table table_name 
DROP primary key;

Let’s create the table employees with 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 syntax to disable the Primary Key using the ALTER INDEX statement in SQL Server (Transact-SQL) is following.

ALTER INDEX constraint_name ON table_name
DISABLE;

Let’s look at the example of how to disable 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 the example of how to enable a primary key using the ALTER INDEX statement in SQL Server.

ALTER INDEX employees_pk ON employees
REBUILD;

Finally, SQL PRIMARY KEY Tutorial With Example is over.

Leave A Reply

Your email address will not be published.

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