SQL PRIMARY KEY

What is a Primary Key?

A PRIMARY KEY is a column (or a combination of columns) in a table that uniquely identifies each row in that table. Each table can have only one primary key, and the values in this key must be unique and cannot be NULL.

Syntax of PRIMARY KEY

The basic syntax for defining a primary key is as follows:

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

Example of Using PRIMARY KEY

Here’s an example of creating a table with a primary key:

CREATE TABLE Students (
  StudentID INT PRIMARY KEY,
  FirstName VARCHAR(50),
  LastName VARCHAR(50)
);

This ensures that the StudentID field is unique for each student.

Composite Primary Key

A composite primary key is a primary key that consists of two or more columns. This is useful for uniquely identifying a record when a single column is not sufficient.

CREATE TABLE Enrollment (
  StudentID INT,
  CourseID INT,
  PRIMARY KEY (StudentID, CourseID)
);

Adding a PRIMARY KEY to Existing Tables

If you need to add a primary key to an existing table, you can use the following command:

ALTER TABLE table_name
ADD CONSTRAINT constraint_name PRIMARY KEY (column_name);

For example:

ALTER TABLE Users
ADD CONSTRAINT pk_userid PRIMARY KEY (UserID);

Important Considerations

When using the PRIMARY KEY constraint, keep in mind:

• Each table can have only one primary key.
• Primary key values must be unique across the table.
• Primary key columns cannot contain NULL values.
• Primary keys are often indexed, improving query performance.