Labels

Different types of constraints in SQL

 Different types of constraints in SQL


1. NOT NULL

  • Definition: Ensures that a column cannot have a NULL value.
  • Purpose: It is used to enforce the requirement that a column must always have a valid (non-null) value.
  • Example: 
    CREATE TABLE Employees (
    ID INT NOT NULL,
    Name VARCHAR(100) NOT NULL
);
  • Difference: The NOT NULL constraint prevents the column from having missing (null) data.

2. UNIQUE

  • Definition: Ensures that all values in a column (or a set of columns) are distinct.
  • Purpose: Used to enforce the uniqueness of the data in a column or combination of columns.
  • Example: 
    CREATE TABLE Employees (
    ID INT,
    Email VARCHAR(100) UNIQUE
);
  • Difference: The UNIQUE constraint allows one or more null values in the column, but ensures that all non-null values are distinct.

3. PRIMARY KEY

  • Definition: A combination of NOT NULL and UNIQUE that uniquely identifies each row in a table.
  • Purpose: Used to uniquely identify each record in a table. A table can have only one primary key, and it can consist of one or multiple columns.
  • Example: 
    CREATE TABLE Employees (
    ID INT PRIMARY KEY,
    Name VARCHAR(100)
);
  • Difference:
    • The primary key is a unique identifier.
    • It implicitly adds a NOT NULL constraint (a primary key column cannot have NULL values).
    • Each table can have only one primary key.

4. FOREIGN KEY

  • Definition: Creates a relationship between two tables by ensuring that a value in one table corresponds to a value in another table, typically referring to the primary key of another table.
  • Purpose: Ensures referential integrity, meaning that the value in the foreign key column must exist in the referenced table.
  • Example: 
    CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    EmployeeID INT,
    FOREIGN KEY (EmployeeID) REFERENCES Employees(ID)
);
  • Difference:
    • A foreign key is a constraint that maintains the relationship between two tables.
    • The foreign key column can contain NULL values unless restricted.

5. CHECK

  • Definition: Ensures that all values in a column meet a specified condition.
  • Purpose: To restrict the values in a column to those that satisfy a given condition or rule.
  • Example: 
    CREATE TABLE Employees (
    ID INT PRIMARY KEY,
    Age INT CHECK (Age >= 18)
);
  • Difference:
    • The CHECK constraint ensures that values satisfy a logical condition (e.g., an age must be 18 or older).
    • Unlike other constraints like UNIQUE, CHECK is based on the values' logical rules.

6. DEFAULT

  • Definition: Specifies a default value for a column if no value is provided during the insertion of a new record.
  • Purpose: Automatically assigns a value to a column when no value is explicitly given.
  • Example: 
    CREATE TABLE Employees (
    ID INT PRIMARY KEY,
    Name VARCHAR(100),
    JoinDate DATE DEFAULT CURRENT_DATE
);
  • Difference:
    • The DEFAULT constraint is used to provide default values (such as the current date) when no value is supplied.
    • Unlike NOT NULL, a column with a DEFAULT constraint can be left blank, but the database will automatically assign the default value.

7. INDEX

  • Definition: Although not strictly a constraint, an index improves the speed of retrieval operations on a table.
  • Purpose: An index is used to increase the speed of search queries on a column by creating a data structure that allows for faster lookups.
  • Example: 
    CREATE INDEX idx_name ON Employees (Name);
  • Difference:
    • An INDEX improves performance and is not a data integrity constraint. It’s used for faster data retrieval, while constraints like NOT NULL or PRIMARY KEY are used to enforce data rules.

8. AUTO_INCREMENT (or SERIAL)

  • Definition: Automatically generates a unique value for a column, typically used for primary key fields.
  • Purpose: Used to automatically increment values in a column with every new row (commonly for the primary key column).
  • Example: 
    CREATE TABLE Employees (
    ID INT AUTO_INCREMENT PRIMARY KEY,
    Name VARCHAR(100)
);
  • Difference:
    • The AUTO_INCREMENT is a feature rather than a strict constraint. It auto-generates unique values, which is particularly useful for primary keys.
    • Not a traditional constraint like NOT NULL or UNIQUE, but helps with managing unique identifiers.

9. NOT ENFORCED Constraints

  • Definition: Some DBMS systems allow defining constraints that are not actively enforced (e.g., foreign key constraints can be defined but not enforced).
  • Purpose: Provides a way to define constraints that are logical but not physically enforced by the system.
  • Example (depending on DBMS): 
    CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT NOT ENFORCED,
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID) NOT ENFORCED
);
  • Difference:
    • NOT ENFORCED constraints are used for logical purposes only. The DBMS does not enforce the rule, but it is still defined in the schema.

Summary of Differences:

Constraint Purpose Uniqueness Allows NULL Allows Duplicate Example Use Case
NOT NULL Ensures no NULL value in the column No No No ID INT NOT NULL
UNIQUE Ensures all values in the column are distinct Yes Yes No Email VARCHAR UNIQUE
PRIMARY KEY Uniquely identifies each record in the table Yes No No ID INT PRIMARY KEY
FOREIGN KEY Ensures data integrity across tables Yes Yes Yes FOREIGN KEY (ID) REFERENCES ...
CHECK Enforces a condition on column values No Yes Yes Age INT CHECK (Age >= 18)
DEFAULT Specifies a default value for a column No Yes Yes JoinDate DATE DEFAULT CURRENT_DATE
INDEX Improves query performance Yes Yes Yes CREATE INDEX idx_name ON Employees (Name)
AUTO_INCREMENT Automatically generates unique values for a column Yes No No ID INT AUTO_INCREMENT

Each constraint plays a different role in maintaining data integrity, and understanding their distinct functionalities is crucial for creating efficient, reliable database designs.

Labels:

Popular Posts