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

Search Keys

  • sql primary key constraint
  • primary key
  • drop primary key
  • 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
  • primary key sql
  • create table primary key
  • sql create table primary key
  • create table with primary key
  • sql 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
  • two primary keys
  • set primary key sql server
  • remove primary key
  • define primary key
  • primary key unique
  • sql insert primary key
  • delete primary key
  • set 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.
105 The Power Of Pl Sql It descripes power of pl SQL programming.
106 Sql Data Analysis It explains how to build and manage SQL database
107 Pl Sql Programming SQL and programming by pl sql.
108 Sql Query Performance How to improve SQL database performance.

Now executing the following below SQL statement:


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

The above SQL statement will produce in an error, because '105' 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;


Search Keys

  • 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
  • sql primary
  • primary key with example
  • sql change primary key value
  • primary constraint
  • sql unique
  • unique constraint
  • unique key in sql
  • sql unique constraint
  • unique keyword in sql
  • index on primary key
  • the 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