In SQL, the PRIMARY KEY constraint is used to uniquely identify each record (row) in a table. It ensures that every value in the specified column or combination of columns is unique and not null. Additionally, a table can have only one PRIMARY KEY constraint.
Here's how you can define a PRIMARY KEY constraint when creating a table:
CREATE TABLE table_name (
column1 datatype PRIMARY KEY,
column2 datatype,
...
);
In this syntax:
table_name: The name of the table you are creating.column1: The name of the column you want to designate as the primary key.datatype: The data type of the column.Example:
Let's say we have a table named employees and we want to ensure that the employee_id column uniquely identifies each employee:
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
...
);
In this example, the employee_id column is defined as the primary key. This means that each employee_id value must be unique within the employees table, and it cannot contain NULL values.
If you try to insert a row with a duplicate employee_id, or if you try to insert a row with a NULL value in the employee_id column, you will receive an error due to the PRIMARY KEY constraint violation.
You can also define a composite primary key, which consists of multiple columns. For example:
CREATE TABLE orders (
order_id INT,
product_id INT,
PRIMARY KEY (order_id, product_id)
);
In this example, the combination of order_id and product_id forms a composite primary key for the orders table. This means that each combination of order_id and product_id must be unique within the orders table.
Using the PRIMARY KEY constraint ensures data integrity by preventing duplicate or null values in the designated key columns. It also facilitates efficient indexing and retrieval of data.