SQL PRIMARY KEY Constraint

Introduction to PRIMARY KEY

The PRIMARY KEY constraint uniquely identifies each record in a database table. It must contain unique values and cannot contain NULL values. Each table can have only one primary key, which can consist of single or multiple columns (a composite key).

Why Use PRIMARY KEY?

A PRIMARY KEY is essential for ensuring that each record in a table is unique and can be easily identified. It also helps in establishing relationships between tables in relational databases.

Syntax

The PRIMARY KEY can be defined when creating a table or added to an existing table. Here’s the basic syntax:

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

You can also define a composite primary key (using multiple columns):

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

Example with Single Column

Let’s create a table called users where the id column is the primary key, ensuring each user has a unique identifier:

CREATE TABLE users (
    id INT PRIMARY KEY,
    username VARCHAR(100) NOT NULL,
    email VARCHAR(255) NOT NULL
);

Example with Composite Key

You can also create a composite primary key using multiple columns. For example, in a table orders, you might want to ensure that the combination of order_id and product_id is unique:

CREATE TABLE orders (
    order_id INT,
    product_id INT,
    order_date DATE,
    PRIMARY KEY (order_id, product_id)
);

Adding PRIMARY KEY to an Existing Table

If you need to add a primary key to an existing table, you can use the ALTER TABLE statement:

ALTER TABLE table_name
ADD PRIMARY KEY (column_name);

Removing PRIMARY KEY Constraint

To remove a primary key from a table, use the ALTER TABLE statement:

ALTER TABLE table_name
DROP PRIMARY KEY;

Conclusion

The PRIMARY KEY constraint is vital for ensuring the integrity and uniqueness of data in your database. It’s a fundamental concept in relational databases, enabling efficient data management and relationships between tables.