PRIMARY KEY in SQL

A PRIMARY KEY uniquely identifies every record in a table.

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    name VARCHAR(100)
);

Here, student_id uniquely identifies each student.

A primary key helps the database identify records accurately.

• Identifies each record uniquely
• Prevents duplicate key values
• Does not allow NULL values
• Helps establish relationships between tables
• Can be used to find a specific record

The basic column-level syntax is:

column_name data_type PRIMARY KEY

Example:

student_id INT PRIMARY KEY

You can define a primary key while creating a table.

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    name VARCHAR(100),
    age INT
);

The student_id column becomes the primary key.

Two records cannot have the same primary key value.

student_id
101
102
103

Each value uniquely identifies a record.

This would create a duplicate key:

101
101

A primary key cannot contain NULL values.

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    name VARCHAR(100)
);

Every student record must have a valid student_id.

An integer column is commonly used as a primary key.

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    name VARCHAR(100)
);

Integer IDs are commonly used because they are simple and efficient identifiers.

A text column can also be used as a primary key if its values are unique.

CREATE TABLE users (
    username VARCHAR(50) PRIMARY KEY,
    name VARCHAR(100)
);

Each username must be unique.

When inserting data, provide a unique primary key value.

INSERT INTO students (student_id, name)
VALUES (101, 'Rahul');

Another record can use a different ID:

INSERT INTO students (student_id, name)
VALUES (102, 'Amit');

If you try to insert a duplicate primary key value, the database will reject it.

INSERT INTO students (student_id, name)
VALUES (101, 'Ravi');

If student_id 101 already exists, this insert causes a duplicate key error.

In MySQL, AUTO_INCREMENT can generate numeric primary key values automatically.

CREATE TABLE students (
    student_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100)
);

The database can generate the student_id when new records are inserted.

When AUTO_INCREMENT is used, you can omit the ID during INSERT.

INSERT INTO students (name)
VALUES ('Rahul');

MySQL generates the primary key value automatically.

The primary key can be used to find a specific record.

SELECT *
FROM students
WHERE student_id = 101;

This searches for the student whose primary key is 101.

A primary key is commonly used to identify the record that should be updated.

UPDATE students
SET name = 'Rahul Kumar'
WHERE student_id = 101;

The record with student_id 101 is updated.

You can use the primary key to delete a specific record.

DELETE FROM students
WHERE student_id = 101;

Only the record with student_id 101 is targeted.

A primary key can also be defined at the table level.

CREATE TABLE students (
    student_id INT,
    name VARCHAR(100),
    PRIMARY KEY (student_id)
);

Here, the primary key is defined separately from the column definition.

You can give a primary key constraint a name.

CREATE TABLE students (
    student_id INT,
    name VARCHAR(100),
    CONSTRAINT pk_students
    PRIMARY KEY (student_id)
);

The constraint is named pk_students.

You can add a primary key to an existing table.

ALTER TABLE students
ADD PRIMARY KEY (student_id);

The student_id column becomes the primary key.

You can add a named primary key constraint to an existing table.

ALTER TABLE students
ADD CONSTRAINT pk_students
PRIMARY KEY (student_id);

The primary key constraint is given the name pk_students.

A composite primary key uses two or more columns together to uniquely identify a record.

CREATE TABLE enrollments (
    student_id INT,
    course_id INT,
    PRIMARY KEY (student_id, course_id)
);

The combination of student_id and course_id must be unique.

Suppose a student can enroll in multiple courses.

student_id | course_id
101        | 1
101        | 2
102        | 1

Here, the combination of student_id and course_id identifies each enrollment.

A primary key in one table can be referenced by a foreign key in another table.

CREATE TABLE courses (
    course_id INT PRIMARY KEY,
    course_name VARCHAR(100)
);

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    course_id INT,
    FOREIGN KEY (course_id)
    REFERENCES courses(course_id)
);

The courses table provides the referenced primary key.

A table has one primary key constraint, although that primary key can contain multiple columns.

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    name VARCHAR(100)
);

You cannot define separate independent primary key constraints for different columns in the same table.

You can remove a primary key constraint using ALTER TABLE.

ALTER TABLE students
DROP PRIMARY KEY;

In MySQL, this removes the primary key constraint from the table.

Both PRIMARY KEY and UNIQUE can enforce uniqueness, but they are not identical.

• PRIMARY KEY uniquely identifies records.
• A table has one primary key constraint.
• Primary key values cannot be NULL.
• UNIQUE is used to prevent duplicate values in a column or combination of columns.
• A table can have multiple UNIQUE constraints.

A common mistake is trying to create a primary key on a column containing duplicate values.

ALTER TABLE students
ADD PRIMARY KEY (student_id);

If duplicate student_id values already exist, the database may reject the operation.

In MySQL, you can use SHOW CREATE TABLE to inspect the table definition.

SHOW CREATE TABLE students;

This can help you check the primary key and other table definitions.

Consider a student table where every student needs a unique ID.

CREATE TABLE students (
    student_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    mobile VARCHAR(15)
);

The student_id uniquely identifies each student and can be generated automatically.

In a student management system, student_id can be used as the primary key.

CREATE TABLE students (
    student_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE,
    course_id INT
);

Other tables can use student_id as a reference when relationships are required.

Here is a complete example using a primary key and a related foreign key.

CREATE TABLE courses (
    course_id INT PRIMARY KEY AUTO_INCREMENT,
    course_name VARCHAR(100) NOT NULL
);

CREATE TABLE students (
    student_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    course_id INT,
    FOREIGN KEY (course_id)
    REFERENCES courses(course_id)
);

INSERT INTO courses (course_name)
VALUES ('Python');

INSERT INTO students (name, course_id)
VALUES ('Rahul', 1);

Here, both tables have their own primary keys, and the students table uses course_id as a foreign key.