SQL Constraints

Overview

Constraints are rules applied to columns in a table that enforce data integrity and restrict the types of data that can be inserted or modified. They help ensure the accuracy and reliability of the data in the database.

Types of SQL Constraints

• NOT NULL: Ensures that a column cannot have a NULL value.
• UNIQUE: Ensures that all values in a column are different from one another.
• PRIMARY KEY: A combination of NOT NULL and UNIQUE. It uniquely identifies each row in a table.
• FOREIGN KEY: Ensures referential integrity by linking rows in one table to rows in another table.
• CHECK: Ensures that all values in a column satisfy a specific condition.
• DEFAULT: Provides a default value for a column when no value is specified during insertion.

Examples

1. NOT NULL Constraint

To create a users table where the email column cannot be NULL:

CREATE TABLE users (
    id INT PRIMARY KEY,
    email VARCHAR(255) NOT NULL
);

2. UNIQUE Constraint

To ensure that the username column has unique values:

CREATE TABLE users (
    id INT PRIMARY KEY,
    username VARCHAR(255) UNIQUE
);

3. PRIMARY KEY Constraint

The id column is set as the primary key:

CREATE TABLE users (
    id INT PRIMARY KEY,
    email VARCHAR(255) NOT NULL
);

4. FOREIGN KEY Constraint

To create a relationship between the orders table and the users table:

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    user_id INT,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

5. CHECK Constraint

To ensure that the age column contains only values greater than or equal to 18:

CREATE TABLE users (
    id INT PRIMARY KEY,
    age INT CHECK (age >= 18)
);

6. DEFAULT Constraint

To set a default value for the status column:

CREATE TABLE users (
    id INT PRIMARY KEY,
    status VARCHAR(10) DEFAULT 'active'
);

Conclusion

SQL constraints are essential for maintaining data integrity in a relational database. By using constraints effectively, you can ensure that your database remains accurate, consistent, and reliable.