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.