8. SQL Constraints

SQL Constraints are rules enforced on the data columns in a table. They are used to limit the type of data that can go into a table, ensuring the accuracy and reliability (integrity) of the data in the database.

Here are the six most common and essential SQL constraints:

---

1. Primary Key 

• Purpose: Uniquely identifies each record (row) in a table. A table can have only one Primary Key.

• Rules: The column(s) designated as the PK must contain unique values and cannot contain NULL values.

ProductID INT PRIMARY KEY

---

2. Foreign Key 

• Purpose: Links two tables together by referencing the Primary Key of another table. It enforces referential integrity.

• Rules: A FK column can contain only values that exist in the PK column of the parent table, or it can be NULL (unless also restricted by NOT NULL).

DepartmentID INT, FOREIGN KEY (DepartmentID) REFERENCES Departments(DeptID)

---

3. NOT NULL

• Purpose: Ensures that a column cannot have a NULL value. A record must always have a value provided for this column.

• Rules: Any attempt to insert or update a row with a NULL value in this column will result in an error.

ProductName VARCHAR(100) NOT NULL

---

4. UNIQUE

• Purpose: Ensures that all values in a column are unique. Unlike a Primary Key, a table can have multiple UNIQUE constraints, and the column can typically contain NULL values (though only one NULL is usually allowed across most database systems).

• Rules: Prevents duplicate entries in that specific column.

EmailAddress VARCHAR(300) UNIQUE

---

5. CHECK

• Purpose: Used to limit the value range that can be placed in a column.

• Rules: The data entered must satisfy the defined logical condition.

• Example: Age INT CHECK (Age >= 18) (Ensures only ages 18 or older can be entered.)

AGE INT CHECK (Age>=18)

---

6. DEFAULT

• Purpose: Provides a default value for a column when a value is not explicitly specified during an INSERT operation.

• Rules: If the column is omitted in the INSERT statement, the predefined default value is automatically used.

StockQuantity INT DEFAULT 0