Constraint in SQL

Constraints in SQL are rules applied to table columns to ensure the accuracy, integrity, and reliability of the data. Constraints can be applied either at the column level or the table level. Below is a breakdown of the different types of constraints in SQL, along with their syntax and examples:

1. NOT NULL Constraint

The NOT NULL constraint ensures that a column cannot have a NULL value. It guarantees that a field must always contain a value.

Syntax:

CREATE TABLE Employees (
EmployeeID INT,
Name VARCHAR(100) NOT NULL, -- NOT NULL constraint
Age INT
);

Example:

In this example, the Name column cannot have NULL values. If a new row is inserted without a name, it will result in an error.

---

1. UNIQUE Constraint

The UNIQUE constraint ensures that all values in a column are distinct. A table can have multiple UNIQUE constraints, and columns can have NULL values (but only one NULL value per column).

Syntax:

CREATE TABLE Users (
UserID INT,
Email VARCHAR(100) UNIQUE, -- UNIQUE constraint
Username VARCHAR(50)
);

Example:

The Email column in this table must have unique values. No two rows can have the same email address.

---

1. PRIMARY KEY Constraint

The PRIMARY KEY constraint uniquely identifies each record in a table. It is a combination of NOT NULL and UNIQUE. Each table can have only one PRIMARY KEY constraint.

Syntax:

CREATE TABLE Orders (
OrderID INT PRIMARY KEY, -- PRIMARY KEY constraint
OrderDate DATE,
CustomerID INT
);

Example:

In this example, OrderID is the primary key. It must be unique and cannot have NULL values.

---

1. FOREIGN KEY Constraint

The FOREIGN KEY constraint is used to create a link between two tables. It ensures that the value in one table corresponds to a value in another table, enforcing referential integrity.

Syntax:

CREATE TABLE Customers (
CustomerID INT PRIMARY KEY,
Name VARCHAR(100)
);

CREATE TABLE Orders (
OrderID INT PRIMARY KEY,
CustomerID INT,
OrderDate DATE,
FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID) -- FOREIGN KEY constraint
);

Example:

In this case, CustomerID in the Orders table is a foreign key that references the CustomerID column in the Customers table. This ensures that each order is associated with a valid customer.

---

1. CHECK Constraint

The CHECK constraint ensures that all values in a column satisfy a specific condition. It allows you to define rules for what data can be stored in a column.

Syntax:

CREATE TABLE Products (
ProductID INT PRIMARY KEY,
Price DECIMAL(10, 2),
Quantity INT CHECK (Quantity >= 0) -- CHECK constraint
);

Example:

Here, the CHECK constraint ensures that the Quantity column always has a value greater than or equal to zero. If someone tries to insert a negative value, it will result in an error.

---

1. DEFAULT Constraint

The DEFAULT constraint provides a default value for a column when no value is specified during the insertion of a row.

Syntax:

CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
Name VARCHAR(100),
HireDate DATE DEFAULT CURRENT_DATE -- DEFAULT constraint
);

Example:

In this case, if no value is provided for the HireDate column during an insert operation, it will automatically use the current date.

---

1. AUTO_INCREMENT / IDENTITY Constraint

The AUTO_INCREMENT (or IDENTITY in some databases) constraint automatically generates a unique value for the column when a new row is inserted. It is commonly used for primary keys.

Syntax (MySQL):

CREATE TABLE Employees (
EmployeeID INT AUTO_INCREMENT PRIMARY KEY, -- AUTO_INCREMENT constraint
Name VARCHAR(100)
);

Example:

In this example, the EmployeeID column will automatically increase by 1 for each new row inserted into the Employees table.

---

Summary of Constraints:

Constraints play a crucial role in maintaining the integrity and accuracy of the data stored in SQL databases.