SQL PRIMARY KEY Constraint

The SQL PRIMARY KEY constraint is used to add unique or distinct value in a field and uniquely identifies each row in a database table.

All Primary key columns must contain PRIMARY KEY values and it will not support NULL value.

A database table can have only ONE SQL PRIMARY KEY constraint column, which may consist of single or multiple columns. When multiple columns are used as a primary key, they are called a composite key.

Difference Between Unique Key and Primary Key

There are many differences between them, which is mentioned below:

	Unique Key	Primary Key
Unique Value	Yes	Yes
Null Value	Support only once in a column	Does not support null value
Column Limit	Support multiple column can have unique key	Support for only one column in a table

You can also search these topics using sql primary key constraint, primary key vs unique key, alter table add primary key, alter table primary key, primary key definition, primary key in sql, primary key in database, create table primary key, sql create table primary key, create table with primary key, oracle add constraint primary key, update primary key, drop primary key sql, create table on primary, sql primary key foreign key, sql update primary key, oracle primary key constraint, composite primary key sql, unique key and primary key, what is primary key in sql, t sql add primary key, create table sql server primary key, set primary key sql server, remove primary key, define primary key, primary key unique, sql insert primary key, delete primary key, add constraint primary key.

SQL PRIMARY KEY Constraint on CREATE TABLE

The following SQL statement creates a SQL PRIMARY KEY constraint on the "BookID" field when the "Books" table is created:

For MySql


CREATE TABLE Books
(
BookID INT NOT NULL,
BookName VARCHAR(255),
Description VARCHAR(255),
PRIMARY KEY(BookID)
);

For Microsoft SQL Server / Oracle / Microsoft Access


CREATE TABLE Books
(
BookID INT NOT NULL PRIMARY KEY,
BookName VARCHAR(255),
Description VARCHAR(255),
BookPrice DECIMAL (8, 2)
);

In the above example, the following SQL statement creates a new table called Books and adds four fields.

Here, BookID field has a SQL PRIMARY KEY constraint to validate newly entered records for unique value.

Let's assume, if the "Books" table already contains the following records:

BookId	BookName	Description
101	Sql Complete Reference	It descripes how to write and execute SQL statement into database.
102	Sql Commands	It exaplins a list of SQL command.
103	Pl Sql Quick Programming	How to write and execute SQL programming using pl sql.
104	Sql Query Injection	How to hack SQL queries in database.

Now executing the following below SQL statement:


INSERT INTO Books (BookID, BookName, Description)
VALUES (103, 'Sql Quick Programming', 'It descripe complete sql programming in short time');

The above SQL statement will produce in an error, because '103' already exists in the BookID field, thus trying to insert another record with that value violates the SQL PRIMARY KEY constraint.

To allow naming of a SQL PRIMARY KEY constraint, and for defining a SQL PRIMARY KEY constraint on multiple fields, use the following SQL syntax:

For Microsoft SQL Server / Oracle / Microsoft Access / MySql


CREATE TABLE Books
(
BookID INT NOT NULL,
BookName VARCHAR(255) NOT NULL,
Description VARCHAR(255),
CONSTRAINT chk_BookID PRIMARY KEY (BookID, BookName)
);

Note: In the example above there is only ONE SQL PRIMARY KEY (chk_BookID). However, the VALUE of the primary key is made up of TWO FIELDS (BookID + BookName).

SQL PRIMARY KEY Constraint on ALTER TABLE

To create a SQL PRIMARY KEY constraint on the "BookID" field when the table is already created, use the following SQL statement:

For Microsoft SQL Server / Oracle / Microsoft Access / MySql


ALTER TABLE Books
ADD PRIMARY KEY (BookID);

To create a SQL PRIMARY KEY constraint on multiple fileds when the table is already created, use the following SQL statement:

For Microsoft SQL Server / Oracle / Microsoft Access


ALTER TABLE Books
ADD CONSTRAINT chk_BookID PRIMARY KEY (BookID, BookName);

Note: If you want to add a primary key using SQL ALTER TABLE statement, the primary key field(s) must already have been declared to not contain NULL values (when the table was first created).

To DROP a SQL PRIMARY KEY Constraint

To drop or remove a SQL PRIMARY KEY constraint, use the following SQL statement:

For MySQL


ALTER TABLE Books
DROP PRIMARY KEY;

For Microsoft SQL SERVER / Microsoft ACCESS / ORACLE


ALTER TABLE Books
DROP CONSTRAINT chk_BookID;

You can also search these topics using example of primary key, add primary key constraint, primary key constraint, primary key multiple columns, multiple primary keys, violation of primary key constraint, sql composite primary key, sql set primary key, multiple field primary key, sql check primary key, sql primary key not null, primary key on two columns, make column primary key, insert primary key, sql server multiple primary keys, create table script with primary key, more than one primary key, primary key table, primary key with example, sql change primary key value, unique key in sql, sql unique constraint, unique keyword in sql, index on primary key, multiple primary keys sql server, identity sql server primary key, primary key two columns, difference between primary key and unique key, difference between primary key and foreign key, sql create table composite primary key.