Data Definition Language (DDL) defines the structure of the database: CREATE makes tables, ALTER changes them, and DROP removes them. The real power is in constraints — rules declared on columns that the database enforces automatically so bad data can never get in.
Constraints enforce integrity
Constraints are guarantees baked into the schema: PRIMARY KEY (unique + not null identifier), FOREIGN KEY (must reference an existing row), NOT NULL, UNIQUE, CHECK (a condition), and DEFAULT (a fallback value).
Create a table with constraints
CREATE TABLE employees (
emp_id INT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
dept_id INT REFERENCES departments(dept_id), -- FK
salary DECIMAL(10,2) CHECK (salary >= 0),
status VARCHAR(10) DEFAULT 'active',
UNIQUE (name)
);Alter and remove
ALTER TABLE employees ADD COLUMN email VARCHAR(100); ALTER TABLE employees DROP COLUMN status; TRUNCATE TABLE employees; -- remove all rows, keep table DROP TABLE employees; -- remove the whole table
⚡ The edge
- DELETE removes rows (filterable, logged, rollback-able); TRUNCATE quickly empties a table (keeps structure); DROP removes the table entirely. Know which one a question is testing.
- A FOREIGN KEY can only reference a column that's a PRIMARY KEY or UNIQUE in the parent table, and it blocks orphan rows — you can't insert a child pointing to a non-existent parent.
Worked example
Which command removes all rows from a table but keeps the table itself?
- DELETE without WHERE removes all rows but is logged row-by-row and can be rolled back.
- TRUNCATE removes all rows in one fast operation, resets identity, and keeps the table structure.
- DROP would remove the entire table, which is not what's asked.
Answer: TRUNCATE TABLE — fast bulk removal of all rows, keeping the table structure.
Worked example
How do you ensure salary can never be negative?
- Use a CHECK constraint on the salary column.
- CHECK (salary >= 0) is enforced on every insert and update.
- Any statement trying to set a negative salary is rejected by the database.
Answer: Add a CHECK (salary >= 0) constraint so the database rejects negative values.
⚠ Watch out
- DROP deletes structure + data; TRUNCATE deletes all rows (keeps table); DELETE deletes selected rows — don't confuse them.
- A FOREIGN KEY must point to a PRIMARY KEY/UNIQUE column, and prevents deleting a parent that still has children (unless ON DELETE CASCADE).
- Choose appropriate data types and lengths — VARCHAR(50) vs TEXT, DECIMAL vs FLOAT for money.