Syntax:

The primary key is typically defined when creating a table using the CREATE TABLE statement.

                                  
                                    CREATE TABLE table_name (
    column1 datatype1 PRIMARY KEY,
    column2 datatype2,
    ...
);
                                  
                                

Example:

Let's create a table named employees with an employee_id column as the primary key:

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

In this example:

• employee_id is the primary key column.
• Its data type is INT.
• It uniquely identifies each employee in the table.

Adding Primary Key to Existing Table:

If you want to add a primary key to an existing table, you can use the ALTER TABLE statement.

                                
                                    ALTER TABLE table_name
ADD PRIMARY KEY (column_name);
                                
                              

For example, if you want to add a primary key to the students table on the student_id column:

                                  
                                    ALTER TABLE students
                                    ADD PRIMARY KEY (student_id);                                    
                                  
                                

Composite Primary Key:

A primary key can consist of multiple columns, forming a composite primary key. Each column in the composite primary key contributes to the uniqueness of the combination.

                                  
                                    CREATE TABLE orders (
    order_id INT,
    product_id INT,
    quantity INT,
    PRIMARY KEY (order_id, product_id)
);                                   
                                  
                                

In this example, the combination of order_id and product_id forms the composite primary key for the orders table.

Auto-incrementing Primary Key:

It's common to use an auto-incrementing primary key, especially for single-column primary keys. This ensures that each new record gets a unique identifier automatically.

                                  
                                    CREATE TABLE books (
    book_id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(100),
    author VARCHAR(50),
    publication_year INT
);                          
                                  
                                

Here, book_id is an auto-incrementing primary key, and each new book added to the table will automatically get a unique identifier.

Constraints and Benefits:

• A primary key must contain unique values, and it cannot contain NULL values.
• Primary keys provide a fast way to uniquely identify each record in a table.
• They are used in relationships between tables (foreign keys) to establish referential integrity.