MySQL Constraints

SQL constraints are rules applied to the data in a table to ensure data integrity and correctness. Constraints can be applied at the column level or table level. They restrict the type of data that can be entered, preventing actions that would violate the rules and maintaining reliable data.

Common SQL Constraints in MySQL:

NOT NULL
- Ensures that a column cannot have a NULL value.
- Column Level: Applied to a specific column.
- Example: To ensure that the email column in a Customers table cannot be NULL:
```
CREATE TABLE Customers (
    CustomerID INT,
    CustomerName VARCHAR(255) NOT NULL,
    Email VARCHAR(255) NOT NULL
);
```
UNIQUE
- Ensures that all values in a column are distinct.
- Can be applied to one or more columns.
- Example: To ensure that the email column in a Customers table contains unique values:
```
CREATE TABLE Customers (
    CustomerID INT,
    CustomerName VARCHAR(255),
    Email VARCHAR(255) UNIQUE
);
```
PRIMARY KEY
- A combination of NOT NULL and UNIQUE. It uniquely identifies each row in the table.
- Only one PRIMARY KEY constraint is allowed in a table, and it often consists of one or more columns.
- Example: To create a primary key on the CustomerID column:
```
CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    CustomerName VARCHAR(255)
);
```
FOREIGN KEY
- Prevents actions that would destroy the links between two tables.
- Enforces referential integrity by ensuring that a value in the foreign key column corresponds to a value in the referenced table's primary key.
- Example: To create a foreign key relationship between the Orders table and the Customers table:
```
CREATE TABLE Orders (
    OrderID INT,
    CustomerID INT,
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);
```
CHECK
- Ensures that the values in a column satisfy a specific condition.
- Example: To ensure that the Age column in the Employees table is greater than 18:
```
CREATE TABLE Employees (
    EmployeeID INT,
    EmployeeName VARCHAR(255),
    Age INT CHECK (Age > 18)
);
```
DEFAULT
- Sets a default value for a column if no value is provided when inserting data.
- Example: To set a default value of 'USA' for the Country column in the Customers table:
```
CREATE TABLE Customers (
    CustomerID INT,
    CustomerName VARCHAR(255),
    Country VARCHAR(255) DEFAULT 'USA'
);
```
CREATE INDEX
- Used to create an index to speed up retrieval of data.
- Indexes are typically created on columns that are frequently queried to improve performance.
- Example: To create an index on the CustomerName column in the Customers table:
```
CREATE INDEX idx_customer_name
ON Customers (CustomerName);
```

Creating Constraints in a Table

Constraints can either be specified during the table creation with the CREATE TABLE statement, or they can be added later using the ALTER TABLE statement.

Example: Creating a Table with Multiple Constraints

CREATE TABLE Employees (
    EmployeeID INT NOT NULL PRIMARY KEY,
    FirstName VARCHAR(255) NOT NULL,
    LastName VARCHAR(255) NOT NULL,
    Email VARCHAR(255) UNIQUE,
    Age INT CHECK (Age >= 18),
    DepartmentID INT,
    FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID)
);

In this example:

EmployeeID is the PRIMARY KEY.
FirstName, LastName cannot be NULL.
Email must be unique.
Age must be greater than or equal to 18.
DepartmentID is a foreign key that links to the Departments table.

Using Constraints with the ALTER TABLE Statement

You can also add or modify constraints after a table is created using the ALTER TABLE statement.

Adding a Foreign Key Constraint

ALTER TABLE Orders
ADD FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID);

Adding a Unique Constraint

ALTER TABLE Customers
ADD CONSTRAINT unique_email UNIQUE (Email);

Summary of Key MySQL Constraints:

NOT NULL: Ensures that a column cannot have NULL values.
UNIQUE: Ensures all values in a column are distinct.
PRIMARY KEY: Combines NOT NULL and UNIQUE to uniquely identify rows.
FOREIGN KEY: Ensures referential integrity between tables.
CHECK: Ensures that data in a column meets specific conditions.
DEFAULT: Specifies a default value for a column.
CREATE INDEX: Optimizes data retrieval with indexes.

By using constraints effectively, you can enforce data integrity and maintain the reliability of your database structure.