DDL queries: creating and shaping databases and tables

So far we created databases and tables by clicking around in phpMyAdmin. Not every company uses phpMyAdmin, so you must know the SQL commands behind those clicks. These are DDL (Data Definition Language) commands: they define structure, not data.

Databases: CREATE and DROP

CREATE DATABASE mydb;

-- safer: no error if it already exists
CREATE DATABASE IF NOT EXISTS mydb;

IF NOT EXISTS uses the EXISTS operator we met in Chapter 5. Without it, creating a database that already exists throws an error.

DROP DATABASE mydb;

-- no error if it doesn't exist
DROP DATABASE IF EXISTS mydb;

SHOW DATABASES;   -- list all databases
USE mydb;         -- make mydb the current database for the following queries

Tables: CREATE, DROP, TRUNCATE

Two ways to create a table: the phpMyAdmin form (name, number of columns, then fill in each column's details and click Go), or one SQL command:

CREATE TABLE users (
    user_id  INTEGER,
    name     VARCHAR(255),
    email    VARCHAR(255),
    password VARCHAR(255)
);

-- or, without an error if it already exists:
CREATE TABLE IF NOT EXISTS users ( … );

Pattern: column_name DATA_TYPE, with a comma after every column definition except the last. Forgetting a comma is the most common error at this stage.

DROP TABLE users;              -- delete the table: structure + data
DROP TABLE IF EXISTS users;

TRUNCATE TABLE users;          -- keep the table, remove ALL rows

In phpMyAdmin both live under the table's Operations tab: "Delete the table (DROP)" and "Empty the table (TRUNCATE)". After a truncate, Browse shows zero rows but the columns are still there.

Constraints: rules that protect integrity

In Chapter 3 we said a database maintains integrity through constraints. Now we write them. Constraints are usually added when you create the table (you can also add or remove them later with ALTER). The course covers seven:

1 · NOT NULL

CREATE TABLE users (
    id    INTEGER NOT NULL,
    name  VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL,
    password VARCHAR(255) NOT NULL
);

Now any INSERT that leaves one of these empty fails. Use it for anything that must always be there (name, email…).

2 · UNIQUE

The value must not repeat across rows; perfect for emails. Two ways to write it:

CREATE TABLE users (
    id    INTEGER NOT NULL,
    name  VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL
);

CREATE TABLE users (
    id    INTEGER NOT NULL,
    name  VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL,
    password VARCHAR(255) NOT NULL,

    CONSTRAINT users_email_unique UNIQUE (email)
);

-- the PAIR (name, email) must be unique;
-- the same name with a different email is fine
CONSTRAINT users_name_email_unique UNIQUE (name, email)

3 · PRIMARY KEY

The column(s) that uniquely identify each row: implicitly unique and not null. Again two ways:

CREATE TABLE students (
    id   INTEGER PRIMARY KEY,
    name VARCHAR(255) NOT NULL
);

CREATE TABLE students (
    id   INTEGER NOT NULL,
    name VARCHAR(255) NOT NULL,

    CONSTRAINT students_pk PRIMARY KEY (id)
    -- composite: PRIMARY KEY (id, name)
);

With the table-level form you can make a composite primary key by listing several columns: then the combination must be unique.

4 · AUTO_INCREMENT

CREATE TABLE passengers (
    id   INTEGER PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL
);

INSERT INTO passengers (name) VALUES ('Nitish');   -- id = 1
INSERT INTO passengers (name) VALUES ('Ankit');    -- id = 2

You don't provide the id; the database looks at the last one and adds 1. This is how you make a surrogate key.

5 · CHECK

Only accept values that satisfy a condition. Example: a school's students must be older than 6 and younger than 25.

CREATE TABLE students (
    id   INTEGER PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    age  INTEGER,

    CONSTRAINT students_age_check CHECK (age > 6 AND age < 25)
);

INSERT INTO students VALUES (1, 'Rahul', 20);  -- ✓
INSERT INTO students VALUES (2, 'Amit', 40);   -- ✗ Check constraint violated

6 · DEFAULT

If an insert doesn't provide a value for the column, use this one instead of NULL. A classic use is recording when a row was created:

CREATE TABLE passengers (
    id     INTEGER PRIMARY KEY AUTO_INCREMENT,
    name   VARCHAR(255) NOT NULL,
    joined DATETIME DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO passengers (name) VALUES ('Nitish');
-- joined is filled with the current date and time automatically

Logical uses: a user's registration time, a default status like 'pending', a default country…

7 · FOREIGN KEY

Links a column to the primary key of another table, enforcing referential integrity: you can't insert an order for a user who doesn't exist, and you can't simply delete a user who still has orders.

CREATE TABLE customers (
    cid   INTEGER PRIMARY KEY AUTO_INCREMENT,
    name  VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE
);

CREATE TABLE orders (
    order_id   INTEGER PRIMARY KEY AUTO_INCREMENT,
    cid        INTEGER NOT NULL,
    order_date DATETIME DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT orders_fk FOREIGN KEY (cid) REFERENCES customers(cid)
);

Notice orders has its own primary key (order_id), while cid is just an integer that must match a customers.cid. In class, trying to drop the parent table while orders still referenced it failed with a foreign-key error, which is exactly the protection we want.

Referential actions: what happens on delete / update

You decide what the database does to child rows when the parent row is deleted or its key is updated:

CONSTRAINT orders_fk FOREIGN KEY (cid) REFERENCES customers(cid)
    ON DELETE CASCADE
    ON UPDATE CASCADE

Remember the branch example from Chapter 3: renaming CSE → CS updates every student automatically with ON UPDATE CASCADE.

ALTER TABLE: changing an existing table

Tables change over time. ALTER TABLE lets you add, delete and modify columns, and add or remove constraints, without recreating the table.

ALTER TABLE students ADD COLUMN college VARCHAR(255) NOT NULL;

-- choose the position (MySQL)
ALTER TABLE students ADD COLUMN college VARCHAR(255) NOT NULL AFTER name;

-- several at once
ALTER TABLE students
    ADD COLUMN city VARCHAR(255) AFTER college,
    ADD COLUMN cgpa DECIMAL(3,2) AFTER city;

ALTER TABLE students DROP COLUMN cgpa;

-- several at once
ALTER TABLE students DROP COLUMN city, DROP COLUMN college;

-- change a column's data type / definition (MySQL)
ALTER TABLE students MODIFY COLUMN name INTEGER;

-- rename a column
ALTER TABLE students RENAME COLUMN name TO full_name;

ALTER TABLE passengers
    ADD CONSTRAINT passengers_email_unique UNIQUE (email);

ALTER TABLE passengers DROP CONSTRAINT passengers_email_unique;
-- older MySQL: ALTER TABLE passengers DROP INDEX passengers_email_unique;

SQL lab: try it yourself

A real SQL engine (SQLite via WebAssembly) running in your browser, with a small translation layer so the MySQL syntax on this page works (AUTO_INCREMENT, TRUNCATE, SHOW TABLES, DESCRIBE). Pick an experiment, read it, run it, then break things on purpose. Errors are the point: each one is a constraint doing its job.

Lab differences from MySQL: there's no CREATE DATABASE (the lab is already one database), MODIFY COLUMN and ADD/DROP CONSTRAINT aren't supported by SQLite, and SQLite is more relaxed about data types.

DDL cheat sheet

Conceptual questions

These check whether you understand why, not just what. Several go one step beyond the video.