In SQL, a PRIMARY KEY is a constraint that is used to uniquely identify each record in a database table. It ensures that the data in the column(s) is unique and not null. A primary key can be composed of a single column or multiple columns (composite primary key).
Key Points About PRIMARY KEY:
1. Uniqueness: Each value in the primary key column must be unique. This ensures that every record in the table can be uniquely identified by its primary key.
2. Not Null: A primary key column cannot have NULL values. Every record must have a valid value for the primary key.
3. Single or Composite Key: A primary key can consist of one column (single key) or multiple columns (composite key) if a single column cannot uniquely identify records.
4. Auto-Increment (Optional):
In many databases (like MySQL), a primary key column can be set to auto-increment, meaning that the database automatically generates unique values for new records.
5. One Primary Key per Table:
A table can have only one primary key. However, the primary key can consist of more than one column (composite primary key).
Syntax for PRIMARY KEY:
1. Single Column Primary Key:
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50)
);
In this example, employee_id is the primary key of the employees table.
2. Composite Primary Key (Multiple Columns):
CREATE TABLE order_items (
order_id INT,
product_id INT,
quantity INT,
PRIMARY KEY (order_id, product_id) — Composite Primary Key
);
Here, the combination of order_id and product_id uniquely identifies each record in the order_items table.
Examples of Primary Key Constraints:
1. Defining a Primary Key in a CREATE TABLE Statement: When creating a table, you can define a primary key directly within the column definition or at the end of the table definition.
In Column Definition:
CREATE TABLE customers (
customer_id INT NOT NULL PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50)
);
At the End of the Table:
CREATE TABLE products (
product_id INT,
name VARCHAR(100),
price DECIMAL(10, 2),
PRIMARY KEY (product_id)
);
2. Using an Existing Table: You can add a primary key to an existing table using the ALTER TABLE statement.
ALTER TABLE employees
ADD CONSTRAINT pk_employee_id PRIMARY KEY (employee_id);
Benefits of PRIMARY KEY:
1. Ensures Data Integrity: By ensuring that each record is unique, the primary key helps maintain data integrity.
2. Faster Data Retrieval: Indexes are automatically created for primary key columns, improving the speed of data retrieval operations like SELECT.
3. Referential Integrity: A primary key can be referenced by foreign keys in other tables, establishing relationships between tables (e.g., one-to-many relationships).
Conclusion:
The PRIMARY KEY is a fundamental concept in SQL used to uniquely identify records in a table. It helps maintain data integrity and is crucial for ensuring the accuracy and consistency of data in relational databases. It can be set on one or more columns and cannot allow NULL values.