For an instructor lead, in-depth look at learning SQL click below.
One of the most fundamental components of SQL-based relational databases is the Primary Key. It is an essential tool used to ensure data integrity through its unique and identifying nature. This blog will dive into the intricacies of the primary key constraint, as well as offering tips on how to master its use.
What is a Primary Key?
In the world of SQL, a Primary Key is a column (or a combination of columns) in a table that uniquely identifies each row in that table. The main characteristic of a Primary Key is that it must contain unique and not null values only. This means you cannot have duplicate values or empty (NULL) values in a Primary Key column.
Declaring a Primary Key
In SQL, declaring a primary key is quite a straightforward task. You only need to include the PRIMARY KEY constraint in your CREATE TABLE statement. It can be done like this:
|
1 2 3 4 5 6 7 8 |
CREATE TABLE Employees( ID INT PRIMARY KEY, Name VARCHAR (100), Age INT, Salary DECIMAL (10, 2) ); |
The above SQL script creates a table named ‘Employees’ with a primary key on the ‘ID’ column. This means that the ‘ID’ column will hold unique and not-null values.
Adding a Primary Key to an Existing Column
If you want to add a Primary Key constraint to an existing column, you can use the ALTER TABLE statement. The syntax looks like this:
|
1 2 3 4 |
ALTER TABLE Employees ADD PRIMARY KEY (ID); |
This SQL statement will add a Primary Key constraint to the ‘ID’ column of the ‘Employees’ table.
Primary Key across Multiple Columns
Sometimes, one column is not enough to uniquely identify a row. In those cases, we use multiple columns as a composite primary key. Here’s how:
|
1 2 3 4 5 6 7 8 |
CREATE TABLE Orders( OrderID INT, OrderNumber INT, CustomerID INT, PRIMARY KEY (OrderID, OrderNumber) ); |
In this example, both ‘OrderID’ and ‘OrderNumber’ together uniquely identify a row, making them the composite primary key.
Conclusion
Understanding how to properly use the Primary Key constraint is foundational to mastering SQL. This powerful tool allows us to enforce data integrity and enables efficient access to data. With this introductory guide on Primary Key Constraint, you are now well on your way to mastering SQL for your data management needs.