Constrints in SQL

In SQL, constraints are rules that define certain limitations or conditions on the data that can be stored in a database table. They help ensure data integrity, maintain consistency, and prevent invalid or inconsistent data from being inserted, updated, or deleted. Constraints play a crucial role in maintaining the accuracy and reliability of a database. Here are some common types of constraints used in SQL:

1. Primary Key Constraint: A primary key constraint ensures that a column or combination of columns uniquely identify each row in a table. It enforces the uniqueness and non-nullability of the specified column(s). A primary key is often used as a reference point for relationships between tables.

Example:

CREATE TABLE Students (
    student_id INT PRIMARY KEY,
    student_name VARCHAR(50)
);

2. Unique Constraint: A unique constraint ensures that values in a column (or a combination of columns) are unique across all rows in the table. Unlike a primary key, a unique constraint allows for NULL values, but it only permits one NULL value.

Example:

CREATE TABLE Employees (
    employee_id INT,
    employee_email VARCHAR(100) UNIQUE,
    ...
);

3. Foreign Key Constraint: A foreign key constraint establishes a relationship between two tables. It ensures that values in a column of one table match values in a primary key column of another table, creating referential integrity. This constraint helps maintain consistency when dealing with related data.

Example:

CREATE TABLE Orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    ...
    FOREIGN KEY (customer_id) REFERENCES Customers(customer_id)
);

4. Check Constraint: A check constraint defines conditions that must be met for values in a column. It limits the range of allowable values based on logical expressions.

Example:

CREATE TABLE Products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100),
    product_price DECIMAL(10, 2),
    ...
    CHECK (product_price > 0)
);

5. Not Null Constraint: A not null constraint ensures that a column cannot contain null values. This constraint enforces data integrity by requiring that a value be present for that column in every row.

Example:

CREATE TABLE Customers (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(100) NOT NULL,
    ...
);

6. Default Constraint: A default constraint assigns a default value to a column when no value is explicitly provided during insertion. It ensures that a valid value is always present, even if not specified.

Example:

CREATE TABLE Orders (
    order_id INT PRIMARY KEY,
    order_date DATE DEFAULT CURRENT_DATE,
    ...
);

Conclusion:

Constraints are essential for maintaining data accuracy, integrity, and consistency within a database. They prevent data that violates predefined rules from entering the database, contributing to a more reliable and manageable data environment. By enforcing these constraints, database administrators and developers can ensure that the data remains trustworthy and useful over time.