Primary Key In MySQL

In MySQL, a primary key is a constraint that uniquely identifies each record (row) in a table. It ensures that the values in the Primary key are unique and not null.

The primary key constraint is crucial for maintaining data integrity and allows for efficient querying and indexing of the table.

Primary Key Syntax In MySQL Table:

CREATE TABLE tableName (
    columnName1 data_type PRIMARY KEY,
    columnName2 data_type,
);

Composite Key Syntax In MySQL Table:

CREATE TABLE tableName (
    columnName1 data_type,
    columnName2 data_type,
    PRIMARY KEY (columnName1, columnName2)
);

How to Add a Primary Key to an Existing MySQL Table:

we can modify the table column ADD PRIMARY KEY using the ALTER TABLE Command.

Syntax:

ALTER TABLE tableName
ADD PRIMARY KEY (columnName);

Removing a Primary Key From Existing MySQL Table:

we can remove a primary key from an existing table using the DROP PRIMARY KEY statement.

Example:

ALTER TABLE products
DROP PRIMARY KEY;

Creating a Table with a Primary Key in MySQL:

In the below example, we have created the `products` table using CREATE TABLE command.

Example:

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(50),
    product_added_date DATE,
    product_code INT,
    product_description VARCHAR(200),
    product_price INT
);

The `products` table is created with columns `product_id`, `product_name`, `product_code`, `product_description`, `product_price` and `product_added_date`.
The `product_id` column is declared as the primary key for the table using the PRIMARY KEY constraint in the Table.
The primary key constraint ensures that each `product_id` value must be unique across all rows in the table and can not be a NULL value.

How to create composite key in MySQL Table:

We can also create a composite primary key using multiple columns in Table.

Example:

CREATE TABLE catalog (
    catalog_id INT,
    product_id INT,
    PRIMARY KEY (catalog_id, product_id)
);

The above combination of "catalog_id" and "product_id" forms the composite primary key, and each combination of these two values must be unique in the table.

Add Data into MySQL Table After Defining Primary Key:

Adding NULL value in Primary key:

The below command will fail because the primary key value cannot be a NULL value.

Syntax:

INSERT INTO products 
(product_id, product_name, product_description, product_added_date, product_code, product_price)
VALUES 
(NULL, 'Ipad', 'Newly Arrival Smart iPad', '2023-01-01', 123, 400);

Adding Correct Data in Primary Key:

The below command will get succeed because we have added a unique value in Primary Key.

Example:

INSERT INTO products 
(product_id, product_name, product_description, product_added_date, product_code, product_price)
VALUES 
("A101", 'Ipad', 'Newly Arrival Smart iPad', '2023-01-01', 123, 400);

Inserting Duplicate Data In Primary Key:

The below command will fail because we are adding a duplicate value in the Primary Key.

Example:

INSERT INTO products 
(product_id, product_name, product_description, product_added_date, product_code, product_price)
VALUES 
("A101", 'Ipad', 'Newly Arrival Smart iPad', '2023-01-01', 123, 400);

<-- Not Null

Foreign Key -->