SQL PRIMARY KEY Constraint

The PRIMARY KEY constraint in SQL is one of the most important constraints used to ensure data integrity in a database table. A primary key uniquely identifies each record in a table, preventing duplicate or NULL values in the specified column(s). Understanding how to properly implement and use the primary key constraint is crucial for managing relational data effectively.

PRIMARY KEY in SQL

PRIMARY KEY in SQL is a column (or group of columns) that uniquely identifies the records in that table. A primary key must contain unique values and can not have any NULL value.

There can only be one primary key in a table, but that primary key can consist of one or more columns. When there are two or more columns in the primary key it is called a composite key.

A primary key automatically has a UNIQUE constraint defined on it, and it ensures that there are no duplicate or NULL values in that column.

SQL PRIMARY KEY Properties

1. No duplicate values are allowed, i.e. The column assigned as the primary key should have UNIQUE values only.
   
2. NO NULL values are present in the Primary key column. Hence there is a Mandatory value in the column having the Primary key.
   
3. Only one primary key per table exists although the Primary key may have multiple columns.
   
4. No new row can be inserted with the already existing primary key.
   
5. Primary keys can be classified into two categories Simple primary key that consists of one column and composite primary key that consists of Multiple column.
   
6. Defined in CREATE TABLE or ALTER TABLE statement.

Syntax

There are two syntaxes to create/add primary key to a table:

• Using CREATE TABLE Statement
• Using ALTER TABLE Statement

SQL PRIMARY KEY with CREATE TABLE

SQL primary key syntax with CREATE TABLE statement is:

CREATE TABLE table_name (
column1 datatype constraint,
column2 datatype constraint,
...,
CONSTRAINT pk_constraint_name PRIMARY KEY (column1, column2, ...)
);

SQL PRIMARY KEY with ALTER TABLE

SQL primary key syntax with ALTER TABLE statement is

ALTER TABLE table_name
ADD CONSTRAINT constraint_name PRIMARY KEY (column1, column2, ... column_n);

SQL PRIMARY KEY Examples

Let's look at some examples of the PRIMARY KEY Constraint in SQL, and understand it's working.

Create PRIMARY KEY in SQL Example

In this example, we will create primary key in a new table using CREATE TABLE statement.

Query

CREATE TABLE Persons ( PersonID int NOT NULL PRIMARY KEY, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int );

Verify SQL Primary key creation

To verify if the primary key has been successfully created, we will try adding duplicate values in primary key column, and SQL should return an error.

Query

INSERT INTO Persons VALUES (1,"Thakur", "Aditya", 22), (1, "Kumar", "Shubham", 21);

Output

Error: UNIQUE constraint failed: Persons.PersonID

Add PRIMARY KEY to a Table Example

In this example, we will add primary key to a already existing table using ALTER TABLE command.

Let's consider previous table, and create it without primary key this time.

CREATE TABLE Persons ( PersonID int, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int);

This query will add primary key to 'Persons' table

ALTER TABLE Persons ADD CONSTRAINT PK_Person PRIMARY KEY (PersonID);

Important Points About SQL PRIMARY KEY

• A primary key is a column or a set of columns in a table that uniquely identifies each row.
• It ensures data integrity by preventing duplicate records and null values.
• A primary key can be defined on a single column (simple primary key) or multiple columns (composite primary key).
• Creating a primary key automatically creates a unique index on the key column(s), improving query performance.
• Establishing relationships between tables using SQL primary key and foreign key improve database design, reduce data redundancy, and improve data consistency.

Benefits of Using Primary Keys

• Data Integrity: The primary key enforces data integrity by ensuring each record is unique.
  
• Efficient Querying: Since a primary key automatically creates an index, querying for records by the primary key is faster.
  
• Referential Integrity: Primary keys are used to establish relationships between tables (via foreign keys), ensuring consistency across related data.

Common Issues and Best Practices

• Avoid NULL values: Always ensure that the columns involved in the primary key do not accept NULL values.
  
• Choose meaningful primary keys: If possible, choose a primary key that naturally fits the data and serves as a meaningful identifier, like an ID field.
  
• Composite Keys: Be cautious when using composite keys. While they are useful in some scenarios, they can make queries more complex. If possible, use a simple key or generate an artificial primary key (like an ID).
  
• Changing Primary Keys: Once a primary key is established, changing it can be difficult because of the interdependencies with other tables (foreign key constraints). Always plan ahead when designing your database schema.

Conclusion

The PRIMARY KEY constraint is a fundamental concept in relational databases that ensures each record in a table is unique and identifiable. By using the primary key effectively, you can maintain data integrity, improve query performance, and establish meaningful relationships between tables. Whether you are working with simple or composite keys, it is important to understand the properties and best practices to ensure a well-designed and efficient database structure.