Constraints on Tables

When managing an online store’s database, it's our responsibility to provide customers with accurate and consistent information about products, orders, and more. For example, we can't store a product without its valid name or an order without a valid customer. To prevent this or any such inconsistency, we need rules that safeguard the integrity of our data. In SQL, these rules are called constraints.

Let's explore how to enforce rules on SQL tables using constraints to maintain data integrity. We'll learn how to:

• Use NOT NULL constraint to ensure the presence of data in mandatory fields.

• Use the UNIQUE constraint to prevent duplicate values in a column.

• Define primary keys using the PRIMARY KEY constraint.

• Establish relationships between tables using the FOREIGN KEY constraint.

• Implement AUTO_INCREMENT to automatically generate unique identifiers.

Constraints in SQL

In SQL, constraints can be specified either when the table is created using the CREATE TABLE statement or after its creation using the ALTER TABLE statement. Some of the commonly used SQL constraints include:

• NOT NULL

• UNIQUE

• PRIMARY KEY

• FOREIGN KEY

• AUTO_INCREMENT

Let’s explore each constraint in detail, one by one.

Setting a NOT NULL constraint

Before we dive into the NOT NULL constraint, it's important to understand NULL values in tables. In SQL, a NULL value represents missing or unknown data. When inserting or updating data, if the original value is missing, unknown, or invalid according to the column's specified type, a NULL is inserted. By default, every column allows NULL values unless we specify otherwise.

There are situations where we might want to ensure that a column always contains a value whenever a new record is inserted. For example, we can't have a NULL value for customer names in the Customers table, because then how would we identify who placed an order? This is where the NOT NULL constraint helps us.

The NOT NULL constraint ensures that a column cannot have a NULL value. When we define a column with the NOT NULL constraint, the database will reject any attempt to insert or update a ...