Constraints

Imagine building an online store. What if an order could be placed without a customer ID, or if two products had the exact same unique product code? Chaos, right? This is where constraints come to the rescue, acting as the guardians of our data’s integrity and reliability. 

By the end of this lesson, we’ll understand 

• What constraints are?

• Why are they so vital?

• How to use various types of constraints in MySQL?

We’ll learn how to define rules that our data must follow, ensuring accuracy and consistency within our database. Specifically, we will cover NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK, and DEFAULT constraints. Let’s get started and see how these rules can make our databases robust and dependable!

What are constraints, and why are they important?

In the world of databases, constraints are rules enforced on data columns in a table. Think of them as gatekeepers that ensure the data entered into our tables is accurate, valid, and consistent. If we try to perform an action that violates a constraint (like inserting a duplicate email if emails are supposed to be unique), the database will reject the action.

Constraints are important for the following reasons:

1. Data integrity: Constraints are fundamental to maintaining data integrity. They prevent bad or inconsistent data from being stored. For instance, we can ensure that an Email column in a Customers table always contains a value and that each email is unique.

2. Accuracy: By setting rules like minimum or maximum values, or ensuring data uniqueness, we improve the overall accuracy of the information stored.

3. Consistency: They help maintain consistency between related tables. For example, a FOREIGN KEY constraint ensures that an order in an Orders table always refers to a valid customer in the Customers table.

4. Business rule enforcement: Constraints allow us to enforce business rules directly at the database level. For example, a product’s price should always be positive, or an order status can only be one of a predefined set of values.

5. Reliability: Databases with well-defined constraints are more reliable because the data they hold adheres to predefined standards.

Without constraints, our database could quickly fill up with incorrect, missing, or nonsensical data, making it unreliable for decision-making and application functionality.

Now, let’s explore the common types of constraints we can use in MySQL.

Types of constraints

MySQL supports several types of constraints that we can apply to our tables. We’ll look at the most commonly used ones, with examples from our OnlineStore database.

1. NOT NULL constraint

Importance: The NOT NULL constraint is vital because it ensures that a specific column cannot have a NULL (empty) value. This is crucial for fields that must always contain data for a record to be meaningful, such as a product’s name or price.

What it is: When a column is defined with a NOT NULL constraint, we must provide a value for that column whenever we insert a new row or update an existing one. Attempting to insert or update a row without a value for a NOT NULL column (or explicitly setting it to NULL) will result in an error.

Sample scenarios and examples:

In our OnlineStore database, the Products table has several columns that cannot be empty:

• ProductName: Every product must have a name.

• Price: Every product must have a price. ...