CREATE TABLE employees (

employee_id INTEGER PRIMARY KEY,

email VARCHAR(100) NOT NULL UNIQUE,

age INTEGER CHECK (age >= 18),

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

hire_date DATE NOT NULL,

is_active BOOLEAN DEFAULT TRUE

);

PRIMARY KEY: Unique identifier, cannot be NULL, one per table

FOREIGN KEY: References another table's primary key

NOT NULL: Field must always have a value

UNIQUE: No duplicate values allowed

CHECK: Custom validation (age >= 18, price > 0)

DEFAULT: Auto-fills value if not provided

Type Mapping: INT: PostgreSQL INTEGER=MySQL INT=SQL Server INT; TEXT: PostgreSQL TEXT=MySQL LONGTEXT

Conversion Order: CAST > TRY_CAST > Implicit (avoid in predicates). JSONB > JSON (indexed)

SERIAL for auto-increment, strict type checking

AUTO_INCREMENT for auto-increment, more lenient

IDENTITY for auto-increment, T-SQL specific types

These types and constraints define what values are allowed and how tables stay consistent. Good schema design starts here.

DATE

Stores a calendar date without any time-of-day component.

DATE '2026-04-18'

TIMESTAMP

Stores date and time together, typically without timezone context.

TIMESTAMP '2026-04-18 14:30:00'

PRIMARY KEY

Uniquely identifies each row and implicitly requires NOT NULL.

customer_id INT PRIMARY KEY

FOREIGN KEY

Enforces referential integrity by requiring a matching row in another table.

FOREIGN KEY (department_id) REFERENCES departments(department_id)

NOT NULL

Prevents missing values in a column that must always contain data.

email VARCHAR(255) NOT NULL

UNIQUE

Prevents duplicate values in a column or column combination.

email VARCHAR(255) UNIQUE

CHECK

Validates a row-level rule whenever data is inserted or updated.