SQL Constraints

Constraints in SQL are used to specify rules for the data in a table. They are used to limit the type of data that can go into a table, ensuring the accuracy and reliability of the data. If there is any violation between the constraint and the data action, the action is aborted. Constraints can be column level or table level.

Here are the most commonly used constraints in SQL:

NOT NULL Constraint: Ensures that a column cannot have a NULL value.

Example:

CREATE TABLE Employees (
    ID int NOT NULL,
    Name varchar(255) NOT NULL
);

UNIQUE Constraint: Ensures that all values in a column are unique.

Example:

CREATE TABLE Employees (
    ID int NOT NULL UNIQUE,
    Name varchar(255) NOT NULL
);

PRIMARY KEY Constraint: Uniquely identifies each record in a database table. Primary keys must contain unique values and cannot be NULL. A table can have only one primary key, which may consist of single or multiple fields.
Example:
```
CREATE TABLE Employees (
    ID int NOT NULL,
    Name varchar(255) NOT NULL,
    PRIMARY KEY (ID)
);
```
FOREIGN KEY Constraint: Uniquely identifies a row/record in another database table. It is used to prevent actions that would destroy links between tables. A foreign key in one table points to a primary key in another table.
Example:
```
CREATE TABLE Orders (
    OrderID int NOT NULL,
    OrderNumber int NOT NULL,
    EmployeeID int,
    PRIMARY KEY (OrderID),
    FOREIGN KEY (EmployeeID) REFERENCES Employees(ID)
);
```
CHECK Constraint: The CHECK constraint is used to limit the value range that can be placed in a column.
Example:
```
CREATE TABLE Employees (
    ID int NOT NULL,
    Age int,
    CHECK (Age >= 18)
);
```

DEFAULT Constraint: Provides a default value for a column when none is specified.

Example:

CREATE TABLE Employees (
    ID int NOT NULL,
    Name varchar(255) NOT NULL,
    City varchar(255) DEFAULT 'Unknown'
);

Please note that the SQL syntax for these constraints can slightly differ between different SQL databases. Therefore, always consult the documentation for the specific database management system (DBMS) you are using.

Primary Key Constraint in SQL:
- Description: Ensures that a column or a combination of columns uniquely identifies each row in a table.
- Code Example:
```
CREATE TABLE ExampleTable (
    ID INT PRIMARY KEY,
    Name VARCHAR(255)
);
```
Unique Constraint in SQL:
- Description: Ensures that values in a column or a combination of columns are unique across the table.
- Code Example:
```
CREATE TABLE ExampleTable (
    Email VARCHAR(255) UNIQUE,
    PhoneNumber VARCHAR(20) UNIQUE
);
```

Foreign Key Constraint in SQL:

Description: Establishes a link between two tables, ensuring referential integrity.

Code Example:

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    ProductID INT,
    FOREIGN KEY (ProductID) REFERENCES Products(ProductID)
);

NOT NULL Constraint in SQL:
- Description: Ensures that a column cannot have NULL values.
- Code Example:
```
CREATE TABLE ExampleTable (
    ID INT NOT NULL,
    Name VARCHAR(255) NOT NULL
);
```

CHECK Constraint in SQL:

Description: Specifies a condition that must be true for each row in a table.

Code Example:

CREATE TABLE ExampleTable (
    Age INT CHECK (Age >= 18),
    Status VARCHAR(10) CHECK (Status IN ('Active', 'Inactive'))
);

Adding Constraints to Existing Tables in SQL:
- Constraints can be added to existing tables using the ALTER TABLE statement.
- Code Example (Adding a Foreign Key):
```
ALTER TABLE Orders
ADD FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID);
```
Dropping Constraints in SQL:
- Constraints can be dropped using the ALTER TABLE statement.
- Code Example (Dropping a Unique Constraint):
```
ALTER TABLE ExampleTable
DROP CONSTRAINT UQ_Email;
```