Types of Constraints in SQL

Types of Constraints in SQL

Constraints in SQL are rules applied to columns or tables to ensure data integrity and enforce business rules. Here’s a comprehensive guide to the main types of constraints:

Primary Key Constraint (PRIMARY KEY)

The PRIMARY KEY constraint ensures that each row in a table is uniquely identifiable. A table can have only one primary key, which can consist of one or more columns.

Rules:

• • Uniqueness: Values must be unique across the table.
  • Non-Null: Values cannot be NULL.

Syntax 

CREATE TABLE table_name (
    column_name data_type PRIMARY KEY,
    ...
);

Example 

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50)
);

Foreign Key Constraint (FOREIGN KEY)

The FOREIGN KEY constraint establishes a link between columns in two tables. It ensures that the value in the foreign key column must exist in the referenced column of another table.

Rules:

• • Reference: The value must exist in the referenced column.
  • Referential Integrity: Deletion or modification of the referenced key must adhere to the defined rules (e.g., CASCADE, SET NULL).

Syntax 

CREATE TABLE table_name (
    column_name data_type,
    foreign_key_column data_type,
    FOREIGN KEY (foreign_key_column) REFERENCES other_table(referenced_column)
);

Example 

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    EmployeeID INT,
    OrderDate DATE,
    FOREIGN KEY (EmployeeID) REFERENCES Employees(EmployeeID)
);

Unique Constraint (UNIQUE)

The UNIQUE constraint ensures that all values in a column or a combination of columns are unique across the table. It allows NULL values but does not allow duplicate non-NULL values.

Rules:

• • Uniqueness: Values must be unique, although NULLs are allowed.

Syntax 

CREATE TABLE table_name (
    column_name data_type UNIQUE,
    ...
);

Example 

CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    ProductName VARCHAR(100) UNIQUE
);

Not Null Constraint (NOT NULL)

The NOT NULL constraint ensures that a column cannot have NULL values. This means that each row must have a value for this column.

Rules:

• • Non-Null: Values cannot be NULL.

Syntax 

CREATE TABLE table_name (
    column_name data_type NOT NULL,
    ...
);

Example 

CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    CustomerName VARCHAR(100) NOT NULL
);

Check Constraint (CHECK)

The CHECK constraint ensures that all values in a column or a combination of columns meet a specific condition or set of conditions.

Rules:

• • Condition: Values must satisfy the defined condition.

Syntax 

CREATE TABLE table_name (
    column_name data_type,
    CHECK (condition),
    ...
);

Example 

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    Salary DECIMAL(10, 2),
    CHECK (Salary >= 0)
);

Default Constraint (DEFAULT)

The DEFAULT constraint provides a default value for a column when no value is specified during an insert operation.

Rules:

• • Default Value: Provides a predefined value if none is provided during insertion.

Syntax 

CREATE TABLE table_name (
    column_name data_type DEFAULT default_value,
    ...
);

Example 

CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    ProductName VARCHAR(100),
    StockQuantity INT DEFAULT 0
);

Composite Key Constraint

Composite keys involve multiple columns used together as a primary key or unique constraint. They are used when a single column is not sufficient to uniquely identify rows.

Rules:

• • Composite Uniqueness: The combination of columns must be unique.

Syntax 

CREATE TABLE table_name (
    column1 data_type,
    column2 data_type,
    ...
    PRIMARY KEY (column1, column2),
    ...
);

Example 

CREATE TABLE OrderDetails (
    OrderID INT,
    ProductID INT,
    Quantity INT,
    PRIMARY KEY (OrderID, ProductID)
);

Table-Level Constraints

Table-level constraints are defined after the column definitions. They are useful for constraints that involve multiple columns.

Rules:

• • Definition: Allows for naming and managing complex or multiple constraints.

Syntax 

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

Example 

CREATE TABLE Sales (
    SaleID INT,
    ProductID INT,
    SaleDate DATE,
    CONSTRAINT unique_sale UNIQUE (SaleID, ProductID)
);

Conclusion

Constraints in SQL play a crucial role in maintaining data quality. They define rules for how data should be inserted, updated, or deleted, ensuring that the data adheres to required standards and business rules. Using constraints such as primary keys, foreign keys, unique constraints, not null constraints, check constraints, and default values helps maintain data consistency and integrity. Constraints can be applied at both the column level and the table level, providing flexibility in how data rules are enforced.