Designing Employee Table with Key SQL Constraints for Data Integrity

Employee Table Design and Constraints

Question

Consider a table named Employee with the following columns:

• employee_id (integer)
• employee_name (varchar)
• email (varchar)
• salary (integer)
• department_id (integer)

Answer the following:

1. Identify and explain three different types of constraints that should be applied to this table to ensure data integrity.
2. Write the SQL CREATE TABLE statement for the Employee table including these constraints:
   • employee_id as the primary key,
   • email must be unique,
   • salary cannot be negative,
   • department_id cannot be null.

---

Answer

i) Three Types of Constraints for the Employee Table

1. Primary Key Constraint: Ensures each record is uniquely identifiable. Applied on employee_id.
2. Unique Constraint: Ensures no duplicate emails. Applied on email.
3. Check and Not Null Constraints: Enforce valid data: salary must be non-negative, department_id must not be null.

ii) SQL CREATE TABLE Statement with Constraints

CREATE TABLE Employee (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100),
    email VARCHAR(255) UNIQUE,
    salary INT CHECK (salary >= 0),
    department_id INT NOT NULL
);

Explanation:

• employee_id is set as the primary key for unique identification.
• email is unique to avoid duplicate email entries.
• salary has a CHECK constraint ensuring it is zero or positive.
• department_id is NOT NULL to guarantee assignment to a department.