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 |
Related Links
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;
Related Links