SQL PRIMARY KEY – Tutorial with Examples

The SQL PRIMARY KEY constraint is a database constraint in SQL that ensures the uniqueness and non-duplication of values within a column or set of columns. It is an important tool in ensuring the accuracy and consistency of data within a database and is used to prevent data redundancy. In addition, it is also used to identify each record in a database table.

Why Use the SQL PRIMARY KEY Constraint?

The PRIMARY KEY constraint is used in databases to ensure that data entered into the database is unique and not duplicated. Consider a database table that stores unique identification numbers. A PRIMARY KEY constraint on the ID column would ensure that each ID is unique and not duplicated within the table. This helps maintain the accuracy and consistency of data in the database and prevents data redundancy.

The PRIMARY KEY constraint is also used to create relationships between tables in a database. This is done by referencing the PRIMARY KEY of one table in another table as a foreign key, which allows you to create relationships between tables and enforce referential integrity in a database.

How to Create a SQL PRIMARY KEY Constraint

A PRIMARY KEY constraint can be added to a database table during its creation or after its creation using the ALTER TABLE statement. To create a PRIMARY KEY constraint when creating a table, you can use the following syntax:

CREATE TABLE employees (
  employee_id INT PRIMARY KEY,
  first_name VARCHAR(50),
  last_name VARCHAR(50),
  department VARCHAR(50)
);

In this example, we are creating a table named “employees” with 4 columns: employee_id, first_name, last_name, and department. The PRIMARY KEY constraint is applied to the “employee_id” column to ensure its uniqueness within the table.

To add a PRIMARY KEY constraint to an existing table, you can use the following syntax:

ALTER TABLE employees
ADD CONSTRAINT pk_employee_id PRIMARY KEY (employee_id);

In this example, we are adding a PRIMARY KEY constraint to the existing “employees” table and naming it “pk_employee_id”. The constraint is applied to the “employee_id” column to ensure its uniqueness within the table.

SQL PRIMARY KEY Constraint and NULL Values

It is important to note that the PRIMARY KEY constraint does not allow NULL values in the columns to which it is applied. For example, if we try to insert a NULL value into the “employee_id” column with a PRIMARY KEY constraint, the database will raise an error. This is because a NULL value in SQL is not considered a unique value and therefore cannot be used as a PRIMARY KEY value in a database table.

Dropping a SQL PRIMARY KEY Constraint

To drop a PRIMARY KEY constraint from a database table, you can use the following syntax:

ALTER TABLE employees
DROP CONSTRAINT pk_employee_id;

In this example, we are dropping the “pk_employee_id” PRIMARY KEY constraint from the “employees” table. After this operation, the “employee_id” column will no longer have a unique constraint, and NULL values may be allowed in the column. However, it is important to consider the impact on data consistency and relationships with other tables before dropping a PRIMARY KEY constraint.

Example of SQL PRIMARY KEY Constraint in Practice

Let’s say we have a database table named “orders” that stores information about customer orders. Each order has a unique order number, and we want to make sure that each order number is unique in the table. To do this, we can add a PRIMARY KEY constraint to the “order_number” column:

CREATE TABLE orders (
  order_number INT PRIMARY KEY,
  customer_name VARCHAR(50),
  order_date DATE,
  total_amount DECIMAL(10, 2)
);

In this example, we are creating a table named “orders” with 4 columns: order_number, customer_name, order_date, and total_amount. The PRIMARY KEY constraint is applied to the “order_number” column to ensure its uniqueness within the table. This helps to maintain the accuracy and consistency of data in the database and prevent data redundancy.

Now, let’s say we want to add a relationship between the “orders” table and a “customers” table. We can do this by referencing the PRIMARY KEY of the “customers” table in the “orders” table as a foreign key:

CREATE TABLE customers (
  customer_id INT PRIMARY KEY,
  customer_name VARCHAR(50),
  address VARCHAR(100),
  email VARCHAR(50)
);
ALTER TABLE orders
ADD CONSTRAINT fk_customer_id
FOREIGN KEY (customer_id) REFERENCES customers (customer_id);

In this example, we are creating a “customers” table with 4 columns: customer_id, customer_name, address, and email. The PRIMARY KEY constraint is applied to the “customer_id” column to ensure its uniqueness within the table. Then, we are adding a foreign key constraint to the “orders” table and naming it “fk_customer_id”. The constraint references the “customer_id” column in the “customers” table, allowing us to create a relationship between the “orders” and “customers” tables and enforce referential integrity in the database.

In conclusion, the SQL PRIMARY KEY constraint is a powerful tool in ensuring the uniqueness, accuracy, and consistency of data in a database. It is used to prevent data redundancy and create relationships between tables. Understanding how to use and implement the PRIMARY KEY constraint is an important aspect of working with databases in SQL.