SQL Tutorial

✨ SQL PRIMARY KEY Constraint

The PRIMARY KEY constraint is a combination of columns in a database table that uniquely identify each row in that table. A table can have only one PRIMARY KEY constraint, and the primary key column(s) must have unique values and cannot contain NULL values.

1️⃣ Creating a Table with PRIMARY KEY

When you create a table, you can define a primary key to uniquely identify each row in the table. Here’s how you can do it during table creation:

CREATE TABLE users (
  user_id INT PRIMARY KEY,
  username VARCHAR(50) NOT NULL,
  email VARCHAR(100) NOT NULL
);

In this example, the user_id column is the PRIMARY KEY, and each user must have a unique user_id value. This ensures there will be no duplicates and helps in identifying rows uniquely.

2️⃣ Inserting Data with PRIMARY KEY

The PRIMARY KEY constraint ensures that the value in the primary key column is unique and not NULL. Here's what happens when you insert data into a table with a primary key:

INSERT INTO users (user_id, username, email)
VALUES (1, 'john_doe', 'john@example.com');

INSERT INTO users (user_id, username, email)
VALUES (1, 'jane_doe', 'jane@example.com');

The second insert will throw an error because the user_id "1" already exists in the table, violating the PRIMARY KEY constraint.

3️⃣ ALTER Table to Add PRIMARY KEY

If a column already exists and you want to add the PRIMARY KEY constraint, you can modify the table using the ALTER TABLE statement. Here's an example:

ALTER TABLE users
ADD PRIMARY KEY (user_id);

This will apply the PRIMARY KEY constraint to the user_id column if it wasn't added during table creation.

4️⃣ Composite PRIMARY KEY

You can also define a PRIMARY KEY on a combination of columns. This is called a composite key. Here's an example:

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

This will prevent duplicate combinations of first_name and last_name in the employees table.

🔧 Key Considerations

• Uniqueness: A PRIMARY KEY must always have unique values. No two rows can have the same value in the PRIMARY KEY column(s).
• NULL Values: A PRIMARY KEY column cannot have NULL values. Every row must have a valid value in the PRIMARY KEY column(s).
• Performance: The PRIMARY KEY automatically creates a clustered index, which improves the performance of queries that search on the PRIMARY KEY.