SQL PRIMARY KEY Keyword

 In SQL, the PRIMARY KEY keyword is used to define a column (or a combination of columns) in a table that uniquely identifies each row in that table. The PRIMARY KEY ensures that the values in these columns are unique and not NULL, enforcing entity integrity in the database.

Key points about PRIMARY KEY:

1. Uniqueness: Each value in the PRIMARY KEY must be unique. This means no two rows can have the same value in the primary key column(s).
2. Non-NULL: A PRIMARY KEY column cannot contain NULL values. Every row must have a valid, non-NULL value in the primary key.
3. Single or Composite Key: A primary key can be a single column or a combination of columns. When multiple columns are used together, it's called a composite primary key.
4. Automatically Indexed: Most database systems automatically create a unique index for the primary key, which speeds up query performance.

Syntax

Single Column Primary Key:

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

Composite Primary Key (Multiple Columns):

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

Example:

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(100),
    LastName VARCHAR(100),
    BirthDate DATE
);

In this example:

• The EmployeeID column is the primary key.
• Every employee must have a unique EmployeeID, and this column cannot contain NULL values.

Altering an Existing Table to Add a Primary Key:

ALTER TABLE Employees
ADD PRIMARY KEY (EmployeeID);

If the table already has data, the values in the EmployeeID column must be unique and non-NULL before the primary key constraint can be added.

Primary Key Constraints:

• If the PRIMARY KEY is defined on multiple columns (composite), the combination of values in those columns must be unique.
• It automatically creates a unique constraint, so there is no need to separately define a UNIQUE constraint on a primary key column.

Primary Key vs Unique Key:

• Primary Key: Cannot have NULL values and guarantees uniqueness.
• Unique Key: Can allow NULL values (depending on the database), but still enforces uniqueness across the column(s).

Conclusion:

The PRIMARY KEY is a fundamental concept in relational databases, ensuring data integrity and uniqueness. It is commonly used in defining the identity of records and optimizing data retrieval.