Column and Table Constraints

Here are some common types of column and table constraints:

1. Column Constraints:

   a. NOT NULL Constraint:
   This constraint ensures that a column must have a value and cannot be NULL. It enforces that data for that column must be provided when inserting or updating records.

   Example:

   CREATE TABLE Employees ( EmployeeID INT PRIMARY KEY, FirstName VARCHAR(50) NOT NULL, LastName VARCHAR(50) NOT NULL, Age INT NOT NULL );

   In this example, the FirstName, LastName, and Age columns are marked as NOT NULL, meaning that these fields must have values whenever a new record is inserted.

   b. UNIQUE Constraint:
   The UNIQUE constraint ensures that each value in the column must be unique across the table. It enforces that no two rows can have the same value for the specified column.

   Example:

   CREATE TABLE Students ( StudentID INT PRIMARY KEY, StudentCode VARCHAR(10) UNIQUE, FirstName VARCHAR(50), LastName VARCHAR(50) );

   In this example, the StudentCode column is marked as UNIQUE, meaning that each student must have a unique student code.

   c. PRIMARY KEY Constraint:
   The PRIMARY KEY constraint uniquely identifies each row in a table. It is a combination of NOT NULL and UNIQUE constraints. A table can have only one primary key.

   Example:

   CREATE TABLE Products ( ProductID INT PRIMARY KEY, ProductName VARCHAR(100), Price DECIMAL(10, 2) );

   In this example, the ProductID column is marked as the primary key.

2. Table Constraints:

   a. PRIMARY KEY Constraint (Table-level):
   Besides applying the PRIMARY KEY constraint to a specific column, you can also define it at the table level, indicating that the combination of columns should be unique.

   Example:

   In this example, the combination of OrderID and ProductID columns forms the primary key for the "Orders" table.

   b. FOREIGN KEY Constraint:
   The FOREIGN KEY constraint establishes a relationship between two tables. It ensures that the values in one table's column (child table) match the values in another table's column (parent table). It enforces referential integrity.

   Example:

   In this example, the CustomerID and ProductID columns in the "Orders" table are foreign keys that reference the corresponding columns in the "Customers" and "Products" tables, respectively.

   c. CHECK Constraint:
   The CHECK constraint allows you to define custom conditions that the data in a column must satisfy.

   Example:

   In this example, the Gender column can only have values 'M' or 'F', due to the CHECK constraint.

These constraints are crucial for maintaining data integrity, consistency, and establishing relationships between tables in a relational database system. They help ensure that the data remains accurate and reliable.