SQL PRIMARY KEY Constraint

 In SQL, the PRIMARY KEY constraint is used to uniquely identify each record in a database table. It ensures that no two rows in the table have the same values in the column or combination of columns that define the primary key. A PRIMARY KEY constraint automatically creates a unique index on the column(s) that makes it more efficient for searching and retrieving data.

Key Characteristics of PRIMARY KEY:

1. Uniqueness: Each value in the primary key column(s) must be unique.
2. Non-null: The primary key column cannot contain NULL values. Every row must have a valid value.
3. Single or Composite: A primary key can consist of a single column or multiple columns (composite primary key).
4. One Primary Key per Table: A table can have only one PRIMARY KEY.

Syntax:

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

For a composite primary key (using more than one column):

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

Example:

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Age INT
);

Example with Composite Primary Key:

CREATE TABLE OrderDetails (
    OrderID INT,
    ProductID INT,
    Quantity INT,
    PRIMARY KEY (OrderID, ProductID)
);

In this case, the combination of OrderID and ProductID will be unique for each row, ensuring that no two rows can have the same pair of values for these columns.