# Database Constraint

A **database constraint** is a rule applied to columns or tables to ensure the integrity, accuracy, and reliability of the data being stored. These constraints help prevent invalid data from being inserted, ensuring consistency across the database. Constraints are essential to protect the structure of a database and maintain proper relationships between data.

Here are the most common types of database constraints, along with multiple examples to help you understand how they work in practice.

---

### 1\. **Primary Key**

A **Primary Key** is a column (or combination of columns) that uniquely identifies each record in a table. A primary key enforces two important rules:

1. **Uniqueness** – No two records can have the same value in this column.
    
2. **Not Null** – The primary key column cannot contain NULL values.
    

#### Examples:

* **Example 1**: In a student database, `student_id` can be the primary key since it uniquely identifies each student.
    
    ```bash
    CREATE TABLE Students (
        student_id INT PRIMARY KEY,
        name VARCHAR(50)
    );
    ```
    
* **Example 2**: In an employee database, `employee_id` can be the primary key.
    
    ```bash
    CREATE TABLE Employees (
        employee_id INT PRIMARY KEY,
        first_name VARCHAR(50),
        last_name VARCHAR(50)
    );
    ```
    
* **Example 3**: A composite primary key uses two or more columns to create a unique identifier.
    
    ```bash
    CREATE TABLE Orders (
        order_id INT,
        product_id INT,
        PRIMARY KEY (order_id, product_id)
    );
    ```
    

---

### 2\. **Foreign Key**

A **Foreign Key** is a column (or a set of columns) in one table that references the **Primary Key** in another table. This creates a relationship between the two tables, ensuring that a record cannot exist in the child table unless it exists in the parent table.

#### Examples:

* **Example 1**: `customer_id` in the `Orders` table is a foreign key that references `customer_id` in the `Customers` table.
    
    ```bash
    CREATE TABLE Orders (
        order_id INT PRIMARY KEY,
        customer_id INT,
        FOREIGN KEY (customer_id) REFERENCES Customers(customer_id)
    );
    ```
    
* **Example 2**: In an `Enrollments` table, `student_id` is a foreign key referencing the `Students` table.
    
    ```bash
    CREATE TABLE Enrollments (
        enrollment_id INT PRIMARY KEY,
        student_id INT,
        course_id INT,
        FOREIGN KEY (student_id) REFERENCES Students(student_id)
    );
    ```
    
* **Example 3**: A foreign key on the `Orders` table references both `customer_id` and `product_id` from separate tables.
    
    ```bash
    CREATE TABLE Orders (
        order_id INT PRIMARY KEY,
        customer_id INT,
        product_id INT,
        FOREIGN KEY (customer_id) REFERENCES Customers(customer_id),
        FOREIGN KEY (product_id) REFERENCES Products(product_id)
    );
    ```
    

---

### 3\. **Unique**

The **Unique** constraint ensures that all the values in a column are different. Unlike a primary key, a table can have multiple unique constraints, and these columns can accept NULL values, but only one NULL is allowed.

#### Examples:

* **Example 1**: In a `Users` table, the `email` column should be unique so that no two users can have the same email address.
    
    ```bash
    CREATE TABLE Users (
        user_id INT PRIMARY KEY,
        email VARCHAR(100) UNIQUE
    );
    ```
    
* **Example 2**: You can create a unique constraint for multiple columns in a `Products` table to ensure no two products have the same combination of `name` and `category`.
    
    ```bash
    CREATE TABLE Products (
        product_id INT PRIMARY KEY,
        name VARCHAR(100),
        category VARCHAR(50),
        UNIQUE (name, category)
    );
    ```
    
* **Example 3**: In a `Vehicles` table, the combination of `license_plate` and `state` must be unique.
    
    ```bash
    CREATE TABLE Vehicles (
        vehicle_id INT PRIMARY KEY,
        license_plate VARCHAR(10),
        state CHAR(2),
        UNIQUE (license_plate, state)
    );
    ```
    

---

### 4\. **Not Null**

The **Not Null** constraint ensures that a column cannot contain NULL values. It guarantees that every row in the table must have a value for the specified column.

#### Examples:

* **Example 1**: In a `Customers` table, the `name` column should always have a value.
    
    ```bash
    CREATE TABLE Customers (
        customer_id INT PRIMARY KEY,
        name VARCHAR(100) NOT NULL
    );
    ```
    
* **Example 2**: A `Products` table where the `price` cannot be NULL.
    
    ```bash
    CREATE TABLE Products (
        product_id INT PRIMARY KEY,
        price DECIMAL(10, 2) NOT NULL
    );
    ```
    
* **Example 3**: The `date_of_birth` column in an `Employees` table should not allow NULL values.
    
    ```bash
    CREATE TABLE Employees (
        employee_id INT PRIMARY KEY,
        date_of_birth DATE NOT NULL
    );
    ```
    

---

### 5\. **Default**

The **Default** constraint provides a default value for a column when no value is specified during insertion. This is useful for fields where most rows will have the same value.

#### Examples:

* **Example 1**: In a `Users` table, if no `status` is provided, it defaults to 'active'.
    
    ```bash
    CREATE TABLE Users (
        user_id INT PRIMARY KEY,
        status VARCHAR(10) DEFAULT 'active'
    );
    ```
    
* **Example 2**: In a `Products` table, if no `stock_quantity` is provided, it defaults to 0.
    
    ```bash
    CREATE TABLE Products (
        product_id INT PRIMARY KEY,
        stock_quantity INT DEFAULT 0
    );
    ```
    
* **Example 3**: In an `Orders` table, the `order_date` defaults to the current date.
    
    ```bash
    CREATE TABLE Orders (
        order_id INT PRIMARY KEY,
        order_date DATE DEFAULT CURRENT_DATE
    );
    ```
    

---

### 6\. **Check**

The **Check** constraint ensures that all values in a column satisfy a specific condition. It enforces data validation at the database level.

#### Examples:

* **Example 1**: In an `Employees` table, the `salary` column must have a value greater than 0.
    
    ```bash
    CREATE TABLE Employees (
        employee_id INT PRIMARY KEY,
        salary DECIMAL(10, 2),
        CHECK (salary > 0)
    );
    ```
    
* **Example 2**: In a `Students` table, the `age` column must ensure the student is at least 18 years old.
    
    ```bash
    CREATE TABLE Students (
        student_id INT PRIMARY KEY,
        age INT CHECK (age >= 18)
    );
    ```
    
* **Example 3**: In a `Products` table, ensure that the `discount` value is between 0 and 100.
    
    ```bash
    CREATE TABLE Products (
        product_id INT PRIMARY KEY,
        discount INT CHECK (discount BETWEEN 0 AND 100)
    );
    ```
    

---

Constraints are important for keeping data accurate, consistent, and fast in databases. Using them correctly helps your database run well with valid data and quick queries. Common constraints like Primary Key, Foreign Key, Unique, Not Null, Default, and Check are necessary to build strong and reliable database systems.

### References:

* [W3Schools: SQL Constraints](https://www.w3schools.com/sql/sql_constraints.asp)
    
* [What is a SQL Constraint - Null, Check, Default, Unique, Primary Key (](https://www.mssqltips.com/sqlservertip/7547/what-is-a-sql-constraint/#:~:text=A%20constraint%20is%20a%20rule%20in%20a%20relational#:~:text=A%20constraint%20is%20a%20rule%20in%20a%20relational)[mssqltips.com](http://mssqltips.com)[)](https://www.mssqltips.com/sqlservertip/7547/what-is-a-sql-constraint/#:~:text=A%20constraint%20is%20a%20rule%20in%20a%20relational#:~:text=A%20constraint%20is%20a%20rule%20in%20a%20relational)
    
* [Studytonight: SQL Constraints](https://www.mssqltips.com/sqlservertip/7547/what-is-a-sql-constraint/#:~:text=A%20constraint%20is%20a%20rule%20in%20a%20relational#:~:text=A%20constraint%20is%20a%20rule%20in%20a%20relational)
    
* [IT Tutorials: Primary, Foreign, Unique, Not Null](https://www.mssqltips.com/sqlservertip/7547/what-is-a-sql-constraint/#:~:text=A%20constraint%20is%20a%20rule%20in%20a%20relational#:~:text=A%20constraint%20is%20a%20rule%20in%20a%20relational)[, Check Constraints](https://ittutorial.org/constraints-primary-key-foreign-key-unique-key-not-null-and-check-in-oracle-sql/)​([IT Tutorial](https://ittutorial.org/constraints-primary-key-foreign-key-unique-key-not-null-and-check-in-oracle-sql-oracle-sql-tutorials-30/))​([W3Schools.com](https://www.w3schools.com/sql/sql_constraints.asp))​([SQL Server Tips](https://www.mssqltips.com/sqlservertip/7547/what-is-a-sql-constraint/))​([StudyTonight](https://www.studytonight.com/dbms/sql-constraints.php))