How to Add a Primary Key in SQL
Palavras-chave:
Publicado em: 04/08/2025How to Add a Primary Key in SQL
A primary key is a crucial constraint in relational databases. It uniquely identifies each record in a table and ensures data integrity. This article explains how to add a primary key to a table in SQL, covering different scenarios and providing best practices.
Fundamental Concepts / Prerequisites
Before diving into the implementation, you should understand the following concepts:
* **SQL (Structured Query Language):** The standard language for managing and manipulating relational databases. * **Tables:** Structures that hold data in rows and columns. * **Constraints:** Rules that enforce data integrity in a table. Primary keys are a type of constraint. * **Unique Constraint:** Ensures that all values in a column are different. A primary key must be unique. * **NOT NULL Constraint:** Ensures that a column cannot contain NULL values. A primary key cannot contain NULL values.Adding a Primary Key During Table Creation
The most common way to add a primary key is during the creation of a table using the `CREATE TABLE` statement.
-- Creating a table with a primary key
CREATE TABLE Customers (
CustomerID INT PRIMARY KEY,
FirstName VARCHAR(255),
LastName VARCHAR(255),
Email VARCHAR(255),
PhoneNumber VARCHAR(20)
);
-- Creating a table with a composite primary key (multiple columns)
CREATE TABLE OrderItems (
OrderID INT,
ProductID INT,
Quantity INT,
PRIMARY KEY (OrderID, ProductID), -- Composite Primary Key
FOREIGN KEY (OrderID) REFERENCES Orders(OrderID),
FOREIGN KEY (ProductID) REFERENCES Products(ProductID)
);
Code Explanation
The first example demonstrates adding a primary key to the `CustomerID` column in the `Customers` table.
The syntax `CustomerID INT PRIMARY KEY` specifies that the `CustomerID` column is an integer and serves as the primary key for the table. This automatically implies that the `CustomerID` column must be unique and not null.
The second example shows a composite primary key for `OrderItems`. It's defined as `PRIMARY KEY (OrderID, ProductID)`. This means that the combination of `OrderID` and `ProductID` must be unique for each row in the `OrderItems` table. FOREIGN KEY constraints are also shown here for clarity (though not directly related to the primary key creation).
Adding a Primary Key to an Existing Table
You can also add a primary key to an existing table using the `ALTER TABLE` statement.
-- Adding a primary key to an existing table
ALTER TABLE Products
ADD PRIMARY KEY (ProductID);
--Adding a primary key to an existing table with a constraint name
ALTER TABLE Products
ADD CONSTRAINT PK_Products PRIMARY KEY (ProductID);
Code Explanation
The `ALTER TABLE Products ADD PRIMARY KEY (ProductID)` statement adds a primary key constraint to the `ProductID` column of the `Products` table. Before running this statement, ensure that the `ProductID` column does not contain any NULL values and that all values are unique.
The `ALTER TABLE Products ADD CONSTRAINT PK_Products PRIMARY KEY (ProductID)` statement does the same thing as the first example, but it also assigns a name (PK_Products) to the primary key constraint. Naming the constraint makes it easier to reference and manage later if needed.
Complexity Analysis
Adding a primary key involves checking the uniqueness and non-nullability of the specified column(s). This check has the following complexity:
* **Time Complexity:** O(n), where n is the number of rows in the table. The database system needs to scan all rows to verify the uniqueness and non-nullability constraints. In some cases, an index might already exist on the column (e.g., created as a unique index) which could optimize the check. * **Space Complexity:** O(1) in most cases. The database system typically doesn't require significant additional memory beyond what's needed for the table itself during the constraint creation process. However, creating an index to support the primary key can require additional storage space, which in the worst-case would be O(n) if all rows are distinct.Alternative Approaches
Instead of adding a primary key directly, you could also create a unique index and then define a separate check constraint for non-nullability. While logically similar, using a primary key constraint is the standard and preferred approach. Manually implementing uniqueness and not-null constraints separately adds unnecessary complexity and might not be optimized by the database engine to the same degree as a primary key constraint.
Conclusion
Adding a primary key is a fundamental operation in SQL for ensuring data integrity. You can define a primary key during table creation or add it to an existing table using the `ALTER TABLE` statement. Always ensure that the column(s) chosen for the primary key have unique and non-null values. Understanding how to work with primary keys is essential for building robust and reliable database applications.