Lecture 39: Ensuring Data Integrity with Constraints

This lecture covers data integrity constraints. Data integrity refers to the accuracy, consistency, and reliability of data stored in a database. We want to prevent bad or invalid data from ever getting into our tables. SQL helps us enforce data integrity by using constraints.

Constraints are rules that are applied to the columns of a table. If you try to perform an action (like an INSERT or UPDATE) that violates a constraint, the database will reject the command and return an error. We have already seen a few of these, but let’s do a full review.

Common SQL Constraints

• PRIMARY KEY: We know this one well. It’s a combination of NOT NULL and UNIQUE. It uniquely identifies each row in a table.
• FOREIGN KEY: Also familiar. It ensures referential integrity by linking a column to the primary key of another table.
• NOT NULL: Ensures that a column cannot have a NULL value. You must provide a value for this column when inserting a row.
• UNIQUE: Ensures that all values in a column are different from one another. You can have multiple UNIQUE constraints on a table.
• CHECK: This is a powerful constraint that allows you to specify a custom rule for the values in a column. The rule must evaluate to TRUE for the value to be allowed.
• DEFAULT: This constraint provides a default value for a column when no value is specified during an INSERT.

Example with Multiple Constraints

Let’s create a new `Employees` table that uses several of these constraints to ensure the data is always valid.

CREATE TABLE Employees (
    EmployeeID INTEGER PRIMARY KEY,
    FirstName TEXT NOT NULL,
    LastName TEXT NOT NULL,
    Email TEXT NOT NULL UNIQUE,
    Salary INTEGER NOT NULL CHECK(Salary > 0),
    EmploymentDate TEXT DEFAULT (date('now'))
);

Let’s break down the constraints on this table:

• EmployeeID INTEGER PRIMARY KEY: Our standard auto-incrementing primary key.
• FirstName TEXT NOT NULL, LastName TEXT NOT NULL: An employee must have both a first and last name.
• Email TEXT NOT NULL UNIQUE: An employee must have an email, and no two employees can have the same email.
• Salary INTEGER NOT NULL CHECK(Salary > 0): An employee must have a salary, and that salary must be a positive number. An attempt to insert a salary of 0 or -100 would fail.
• EmploymentDate TEXT DEFAULT (date('now')): If we don’t specify an employment date when inserting a new employee, the database will automatically fill it with the current date.

In the Next Lecture…

We are ready to begin the fifth and final part of our series: Putting It All Together. We will start our final, most comprehensive project: building a Student Enrollment System from scratch. We will begin, as always, with the design phase.

Lecture 38 – Handling Missing Data with NULL