Employee Table with Constraints

basic

Create employee table with all constraint types

CREATE TABLE employees (

employee_id INTEGER PRIMARY KEY,

email VARCHAR(100) NOT NULL UNIQUE,

first_name VARCHAR(50) NOT NULL,

age INTEGER CHECK (age >= 18 AND age <= 100),

salary DECIMAL(10,2) CHECK (salary > 0),

department_id INTEGER,

hire_date DATE NOT NULL,

is_active BOOLEAN DEFAULT TRUE,

FOREIGN KEY (department_id) REFERENCES departments(department_id)

);

Constraints enforced:

• PRIMARY KEY: employee_id uniquely identifies each employee

• NOT NULL: email, names, hire_date are required

• UNIQUE: email cannot be duplicated

• CHECK: age 18-100, salary must be positive

• FOREIGN KEY: department_id must exist in departments table

• DEFAULT: is_active defaults to TRUE

Constraints prevent invalid data at database level. Cannot insert employee with negative salary or duplicate email.

All

DECIMAL vs FLOAT for Money

basic

Why DECIMAL is required for financial data

-- ❌ WRONG: Using FLOAT

CREATE TABLE orders_bad (

total FLOAT

);

INSERT INTO orders_bad VALUES (10.10);

SELECT total FROM orders_bad;

-- Returns: 10.099999904632568 (ROUNDING ERROR!)

-- ✅ CORRECT: Using DECIMAL

CREATE TABLE orders_good (

total DECIMAL(10,2)

);

INSERT INTO orders_good VALUES (10.10);

SELECT total FROM orders_good;

-- Returns: 10.10 (EXACT!)