Enforcing Rules with SQL Constraints

When designing a database, ensuring data integrity and accuracy is critical. SQL Constraints are rules applied to columns in a table to enforce consistent, valid data entry. They are essential for creating reliable and well-structured databases.

Get Morty’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

In this article, we’ll explore the different types of SQL constraints, their syntax, and examples of how to use them effectively.

What Are SQL Constraints?

SQL Constraints define rules for data in a table. They restrict the values that can be inserted, ensuring that only valid data is stored. If a value violates a constraint, the database rejects the operation.

Common SQL Constraints

1. NOT NULL: Ensures that a column cannot have a NULL value.
2. UNIQUE: Ensures all values in a column are distinct.
3. PRIMARY KEY: Combines NOT NULL and UNIQUE to uniquely identify a row in a table.
4. FOREIGN KEY: Ensures the value in a column matches a value in another table, establishing a relationship.
5. CHECK: Ensures that values in a column meet a specific condition.
6. DEFAULT: Assigns a default value to a column if no value is provided.

Syntax for SQL Constraints

Adding Constraints During Table Creation

CREATE TABLE table_name (
    column1 data_type constraint_name,
    column2 data_type constraint_name,
    ...
);

Adding Constraints to Existing Tables

ALTER TABLE table_name
ADD CONSTRAINT constraint_name constraint_definition;

Practical Examples

Let’s work with the following scenario: creating an employees table.

Example 1: NOT NULL and UNIQUE

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

• NOT NULL: Ensures employee_id and first_name cannot be NULL.
• UNIQUE: Ensures employee_id and email values are unique.

Example 2: PRIMARY KEY

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

• Combines NOT NULL and UNIQUE to ensure employee_id uniquely identifies each row.

Example 3: FOREIGN KEY

To link employees to their departments:

CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(50)
);

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    department_id INT,
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

FOREIGN KEY: Ensures department_id in employees matches a valid department_id in departments.

Example 4: CHECK Constraint

To ensure valid salary ranges:

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    salary DECIMAL(10, 2),
    CHECK (salary >= 30000 AND salary <= 200000)
);

CHECK: Ensures salary is between $30,000 and $200,000.

Example 5: DEFAULT Constraint

To set a default department:

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    department_id INT DEFAULT 101
);

• DEFAULT: Assigns department 101 to employees if no value is provided.

Common Mistakes to Avoid

1. Overusing Constraints: Too many constraints can complicate database operations and reduce flexibility.
2. Mismatched FOREIGN KEYS: Ensure referenced columns have the same data type.
3. Ignoring CHECK Logic: Test your constraints thoroughly to avoid unexpected rejections.

Conclusion

SQL Constraints are fundamental for maintaining data integrity and ensuring a well-structured database. By using constraints wisely, you can prevent invalid data and reduce the need for manual data validation. Practice applying these constraints in your database designs to master their usage.

In the next article, we’ll explore SQL Functions, where you’ll learn how to use built-in functions to manipulate and analyze your data efficiently.