SQL PRIMARY KEY

PRIMARY KEY

The SQL PRIMARY KEY is a column in a table which must contain a unique value which can be used to identify each and every row of a table uniquely.

However, SQL supports primary keys directly with the PRIMARY KEY constraint.

Functionally, it is the same as the UNIQUE constraint, except that only one PRIMARY KEY can be defined for a given table. PRIMARY KEY's will not allow NULL values.

A primary key is used to identify each row identically in a table. It may be a part of the actual record itself.

The SQL PRIMARY KEY can be made up by one or more fields on a table and when it happens, they are called a composite key.

Primary keys can be specified at the time of CREATING TABLE or the time of changing the structure of the existing table using ALTER TABLE statement.

This constraint is a combination of a NOT NULL constraint and a UNIQUE constraint. This constraint ensures that the specific column or combination of two or more columns for a table have a unique identity which helps to find a particular record in a table more easily and quickly.

Syntax:

CREATE TABLE <table_name>
column1    data_type[(size)]  NOT NULL  PRIMARY KEY,
column2    data_type[(size)],
...);

Parameters:

Good practice for primary keys in tables

• Primary keys should be as small as necessary. Prefer a numeric type because numeric types are stored in a much more compact format than character formats.
• Primary keys should never change.
• Do not use passport number, social security number, or employee contract number as "primary key" as these "primary key" can change for real world situations.

Example:

Suppose, we are going to create a table named 'agent1'. It contains the columns and data types which shown bellow. For each row of 'agent1' table, it is required to identify each agent with a unique code, because the name of two or more agents of a city of a country may be same.

So, it is not a good choice to create PRIMARY KEY on 'agent_name'. The 'agent_code' could be the only and exclusive choice for a PRIMARY KEY for this table.

While creating a table you can include a primary key using column-level primary-key constraint or table-level constraint. Here are two examples on the said table :

Column-level primary-key constraint :

SQL Code:

CREATE TABLE agent1(
<span class="style1">agent_code char(6) NOT NULL PRIMARY KEY</span>,
agent_name char(40) NOT NULL,
working_area char(35),
commission decimal(10,2),
phone_no char(17)
) ;

Table-level primary-key constraint:

SQL Code:

CREATE TABLE agent1(
agent_code char(6) NOT NULL,
agent_name char(40) NOT NULL,
working_area char(35),
commission decimal(10,2),
phone_no char(17),
<span class="style1">CONSTRAINT pk_agent_code PRIMARY KEY (agent_code))</span>;
) ;

SQL CREATE TABLE with PRIMARY KEY CONSTRAINT

SQL PRIMARY KEY CONSTRAINT is a combination of a NOT NULL constraint and a UNIQUE constraint. This constraint ensures that the specific column or combination of two or more columns for a table have an unique identity which helps to find a particular record in a table more easily and quickly.

Example :

The following example creates a table. Here is the field name and data types :

the following SQL statement can be used :

SQL Code:

CREATE TABLE mytest(
agent_code char(6) NOT NULL PRIMARY KEY,
agent_name char(40) NOT NULL,
working_area char(35),
commission decimal(10,2),
phone_no char(17));

To see the structure of the created table :

SQL Code:

DESCRIBE mytest; 

Output :

SQL CREATE TABLE with PRIMARY KEY and UNIQUE CONSTRAINT

The following example creates a table. Here is the field name and data types:

the following SQL statement can be used:

SQL Code:

CREATE TABLE mytest(
cust_code char(6) NOT NULL PRIMARY KEY,
cust_name	char(25) NOT NULL UNIQUE,
cust_city char(25) NOT NULL,
grade	integer,
agent_code char(6) NOT NULL);

To see the structure of the created table :

SQL Code:

DESCRIBE mytest;

Output:

SQL CREATE TABLE with PRIMARY KEY CONSTRAINT on more columns

In the following topic, we are going to discuss the usage of SQL PRIMARY KEY CONSTRAINT along with the CREATE TABLE statement for two or more columns.

Example:

The following example creates a table. The table must contain a PRIMARY KEY with the combination of two columns 'cust_code' and 'cust_city'. Here is the field name and data types :

the following SQL statement can be used:

SQL Code:

CREATE TABLE mytest(
cust_code char(6) NOT NULL,
cust_name char(25) NOT  NULL,
cust_city char(25),
grade integer,
agent_code char(6) NOT NULL,
PRIMARY KEY (cust_code,cust_city));

To see the structure of the created table:

SQL Code:

DESCRIBE mytest;

Output:

Practice SQL Exercises

• SQL Exercises, Practice, Solution
• SQL Retrieve data from tables [33 Exercises]
• SQL Boolean and Relational operators [12 Exercises]
• SQL Wildcard and Special operators [22 Exercises]
• SQL Aggregate Functions [25 Exercises]
• SQL Formatting query output [10 Exercises]
• SQL Quering on Multiple Tables [8 Exercises]
• FILTERING and SORTING on HR Database [38 Exercises]
• SQL JOINS
• SQL JOINS [29 Exercises]
• SQL JOINS on HR Database [27 Exercises]
• SQL SUBQUERIES
• SQL SUBQUERIES [39 Exercises]
• SQL SUBQUERIES on HR Database [55 Exercises]
• SQL Union[9 Exercises]
• SQL View[16 Exercises]
• SQL User Account Management [16 Exercise]
• Movie Database
• BASIC queries on movie Database [10 Exercises]
• SUBQUERIES on movie Database [16 Exercises]
• JOINS on movie Database [24 Exercises]
• Soccer Database
• Introduction
• BASIC queries on soccer Database [29 Exercises]
• SUBQUERIES on soccer Database [33 Exercises]
• JOINS queries on soccer Database [61 Exercises]
• Hospital Database
• Introduction
• BASIC, SUBQUERIES, and JOINS [39 Exercises]
• Employee Database
• BASIC queries on employee Database [115 Exercises]
• SUBQUERIES on employee Database [77 Exercises]
• More to come!

Want to improve the above article? Contribute your Notes/Comments/Examples through Disqus.

Previous:Create Table
Next: Foreign Key