AppDividend
Latest Code Tutorials

SQL Primary Key Example | Primary Key in SQL Tutorial

0

SQL primary key is a field in a table that is used for uniquely identifying a row in a table. If the 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

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 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 entity integrity of the table.
  2. The primary key always has 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 the 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 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 creation of the table, we had not mentioned any column as a primary key. So, to make column(s) unique, we had used an alter 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 syntax to disable the Primary Key using the ALTER INDEX statement in SQL Server (Transact-SQL) is the 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 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.