SQL Tutorial: Employee & Client Tables with Constraints and Foreign Key

SQL Tutorial – Employee and Client Tables with Constraints and Foreign Key

This tutorial demonstrates how to create Employee and Client tables in SQL with appropriate data types, constraints, and foreign key relationships. 

1. Employee Table Schema

SQL CREATE TABLE statement:

CREATE TABLE Employee (
    Emp_id INT PRIMARY KEY,
    Name VARCHAR(50) NOT NULL,
    Department VARCHAR(50) NOT NULL,
    Salary DECIMAL(10,2) CHECK (Salary >= 0),
    Joining_Year YEAR
);

Employee Table Data Example

Emp_id	Name	Department	Salary	Joining_Year
101	Arifa	HR	35000	2020
102	Laboni	IT	55000	2021
103	Shabnam	Finance	48000	2019
104	Mitu	IT	60000	2022
105	Hasna	Marketing	42000	2020

---

2. Client Table Schema with Foreign Key

SQL CREATE TABLE statement:

CREATE TABLE Client (
    Client_id INT PRIMARY KEY,
    Name VARCHAR(50) NOT NULL,
    Contact_no VARCHAR(15) UNIQUE,
    Email VARCHAR(50) UNIQUE,
    City VARCHAR(50),
    Department VARCHAR(50),
    CONSTRAINT fk_department
        FOREIGN KEY (Department)
        REFERENCES Employee(Department)
        ON DELETE SET NULL
        ON UPDATE CASCADE
);

Client Table Data Example

Client_id	Name	Contact_no	Email	City	Department
201	Rafia	017XXXXXXXX	rafia@mail.com	Dhaka	HR
202	Sabrina	018XXXXXXXX	sabrina@mail.com	Chittagong	IT
203	Karima	019XXXXXXXX	karima@mail.com	Dhaka	Finance

---

3. Explanation of Data Types and Constraints

• Emp_id / Client_id: INT, serves as PRIMARY KEY for unique identification.
• Name: VARCHAR(50), NOT NULL to ensure a name is provided.
• Department: VARCHAR(50), FOREIGN KEY in Client referencing Employee.Department to maintain referential integrity.
• Salary: DECIMAL(10,2) with CHECK to prevent negative values.
• Contact_no / Email: VARCHAR with UNIQUE to prevent duplicates.
• Joining_Year: YEAR data type to store the joining year of employees.

---

4. Foreign Key Behavior

• ON DELETE SET NULL: If a department is deleted from Employee, the Client’s Department will be set to NULL.
• ON UPDATE CASCADE: If a department name is updated in Employee, the Client table will automatically reflect the change.