SQL - Maintain Data Integrity with Constraints

To ensure the integrity and consistency of your database, use SQL constraints such as PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, and CHECK. Constraints help enforce rules and relationships within your data, preventing incorrect or invalid data from being inserted or updated.

Example:
Suppose you have a table called "employees" and you want to ensure that each employee's email address is unique.

Without a Constraint (Potential Data Duplication):

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100)
);

With a UNIQUE Constraint (Enforces Data Integrity):

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100) UNIQUE
);

By adding a UNIQUE constraint to the "email" column, you prevent duplicate email addresses from being inserted into the "employees" table. This ensures that each email address remains unique, maintaining data integrity and preventing data inconsistencies.

SQL constraints are essential for enforcing business rules, maintaining data quality, and preventing errors or conflicts within your database.

Comments

[Nathan S.R.]

Thanks for this very useful post. I just wanted to add that, there is a very easy way now, to test all the SQLs described here, using the free & portable tools, mentioned in my latest post here : dev.to/linuxguist/learn-sql-quickl...