Idriss Rami — Notes

SQL DDL

2 min read

DDL (Data Definition Language) is the subset of SQL used to define database structures — the schema rather than the data. The keywords are:

  • CREATE — make a new database, table, index, or view.
  • ALTER — modify the structure of an existing object: add a column, add a constraint, change a column’s type.
  • DROP — permanently remove an object. The data goes with the structure.
  • RENAME — change the name of an object.
  • TRUNCATE — empty a table without logging each row deletion individually. Fast but irreversible.

A typical DDL session looks like:

CREATE DATABASE IF NOT EXISTS onq;
USE onq;
 
CREATE TABLE students (
    student_id   INT NOT NULL,
    first_name   VARCHAR(255),
    family_name  VARCHAR(255),
    phone        CHAR(20),
    major_code   INT,
    PRIMARY KEY (student_id)
);
 
ALTER TABLE students
ADD UNIQUE KEY (phone);
 
ALTER TABLE students
ADD COLUMN student_status ENUM('P', 'F') AFTER family_name;

The IF NOT EXISTS clause on CREATE DATABASE is a small protection against running the script twice — it creates the database only if one with that name doesn’t already exist. The USE onq makes onq the current database so later commands don’t need to qualify table names.

The AFTER family_name clause on ALTER TABLE ... ADD COLUMN is cosmetic — it controls where the new column appears in the table layout. It doesn’t affect behaviour, just readability.

The other three SQL sub-languages — DML for working with data, DCL for permissions, TCL for transactions — handle different concerns. DDL is specifically for the structure: tables, columns, constraints, indices.

TRUNCATE versus DELETE. Both empty out rows, but TRUNCATE is DDL (treated as a structural reset) while DELETE FROM table_name without a WHERE is DML. TRUNCATE is much faster on large tables because it doesn’t log every row. DELETE can be rolled back inside a transaction on any database. TRUNCATE is rollback-safe in PostgreSQL and SQL Server (it runs inside a transaction and undoes cleanly) but performs an implicit commit and cannot be rolled back in MySQL or Oracle. Categorization is dialect-dependent — MySQL treats TRUNCATE as DDL; PostgreSQL classifies it more loosely.

The classification of these five keywords as DDL is fairly standard, but note that TRUNCATE is sometimes grouped with DML in other taxonomies because it removes data rather than redefining structure.

Graph View

Backlinks