SQL Constraints (PRIMARY KEY, FOREIGN KEY, NOT NULL, UNIQUE, CHECK)

Structured Query Language (SQL) is a fundamental tool in the world of data, used to create, manipulate, and manage data stored in relational databases. One of the pivotal components in SQL are constraints, which are rules applied to columns or tables to prevent unwanted or inaccurate data from being stored. Constraints are essential in maintaining the integrity and reliability of the data in your database. This blog post is designed to provide a comprehensive, yet accessible understanding of SQL constraints, namely PRIMARY KEY, FOREIGN KEY, NOT NULL, UNIQUE, and CHECK. With practical examples and explanations, beginners and professionals alike will find this guide helpful in enhancing their SQL knowledge.

PRIMARY KEY Constraint

The PRIMARY KEY constraint uniquely identifies each record in a table. Primary keys must contain unique values and cannot contain null values. A table can have only one primary key, which may consist of single or multiple fields.

Example

Let's consider a simple "Students" table:

CREATE TABLE Students (
    StudentID int,
    StudentName varchar(255),
    Age int,
);

Now, let's add a PRIMARY KEY constraint to the StudentID field:

ALTER TABLE Students
ADD PRIMARY KEY (StudentID);

In this case, the StudentID is the primary key, ensuring that each student in the table is uniquely identified by their StudentID.

FOREIGN KEY Constraint

A FOREIGN KEY is a field (or collection of fields) in a table, that is the primary key of another table. The FOREIGN KEY constraint is used to prevent actions that would destroy links between tables.

Example

Consider another table, "Orders":

CREATE TABLE Orders (
    OrderID int,
    OrderNumber int,
    StudentID int
);

Here, StudentID is a FOREIGN KEY in the "Orders" table:

ALTER TABLE Orders
ADD FOREIGN KEY (StudentID)
REFERENCES Students(StudentID);

This ensures that the StudentID in the "Orders" table matches with StudentID in the "Students" table, maintaining relational integrity.

NOT NULL Constraint

By default, a column in a SQL table can hold null values. The NOT NULL constraint enforces a column to not accept null values, ensuring that a column always has a value.

Example

Let's enforce that the StudentName and Age columns always have a value:

CREATE TABLE Students (
    StudentID int PRIMARY KEY,
    StudentName varchar(255) NOT NULL,
    Age int NOT NULL
);

In this case, the "Students" table cannot have any record where StudentName or Age is null.

UNIQUE Constraint

The UNIQUE constraint ensures that all values in a column are different. Unlike the PRIMARY KEY constraint, you can have multiple UNIQUE constraints in a table, with the ability to accept null values.

Example

We can add a UNIQUE constraint to the OrderNumber column in the "Orders" table:

ALTER TABLE Orders
ADD UNIQUE (OrderNumber);

This ensures that no two orders can have the same OrderNumber.

CHECK Constraint

The CHECK constraint enforces that all values in a field satisfy a certain condition. This is used to ensure the validity of the data in a column.

Example

We can add a CHECK constraint to the Age column in the "Students" table to ensure all students are above 18:

ALTER TABLE Students
ADD CHECK (Age>=18);

This constraint guarantees that no student is less than 18 years old.

FAQ

1. What is the difference between UNIQUE and PRIMARY KEY constraints?

The PRIMARY KEY constraint uniquely identifies each record in a database table. Primarykeys must contain unique, non-null values. A UNIQUE constraint also ensures that all values in a column are distinct, but unlike a primary key, it allows for null values. Also, while a table can have multiple UNIQUE constraints, it can only have one PRIMARY KEY constraint.

2. Can a table have two PRIMARY KEY constraints?

No, a table can have only one PRIMARY KEY constraint. However, the primary key can be made up of multiple columns, also known as a composite primary key.

3. Can FOREIGN KEY be null?

Yes, a FOREIGN KEY can hold null values. A null foreign key indicates that a certain record doesn't necessarily relate to any record in the referenced table.

4. Can I remove a constraint from a column?

Yes, constraints can be removed from a column using the ALTER TABLE statement. For example, to remove a CHECK constraint, you would use:

ALTER TABLE Students
DROP CHECK constraint_name;

Remember to replace 'constraint_name' with the name of your CHECK constraint.

5. What is the role of constraints in SQL?

Constraints in SQL are used to specify rules for the data in a table. This helps ensure accuracy, reliability, and integrity of the data. If there is any violation between the constraint and the action being performed, then that action is aborted.

Constraints are an essential aspect of maintaining the reliability of data within a database. As you develop your SQL skills, you'll find yourself utilizing these constraints to not only ensure data integrity, but also manage relationships between tables. By mastering PRIMARY KEY, FOREIGN KEY, NOT NULL, UNIQUE, and CHECK constraints, you will be well-equipped to build and manage robust, reliable databases.