SQL Basics Part 1: DDL, DML, Constraints & Keys

This is the first post in what I’m planning as a small series on learning SQL properly, from the ground up. I’m writing it as a tutorial rather than just personal notes, because explaining something clearly enough for a beginner to follow is usually the fastest way to check if I actually understand it myself.

Today’s focus: creating a database, defining a table, inserting data, and finally getting a real grip on constraints and the different types of keys (a topic that confused me for longer than I’d like to admit).

DDL vs. DML: The First Distinction to Get Right

Before writing a single line of SQL, it helps to know which “category” a command falls into:

• DDL (Data Definition Language): commands that define or change the structure of your database: CREATE, ALTER, DROP, TRUNCATE.
• DML (Data Manipulation Language): commands that work with the data itself: INSERT, UPDATE, DELETE, SELECT.

Think of DDL as building the shelves, and DML as putting things on them.

Step 1: Creating a Database (DDL)

Everything starts with a database to hold our tables:

CREATE DATABASE sales;
USE sales;

CREATE DATABASE builds the container. USE tells MySQL: “everything I run next applies inside this database.” Skip USE and you’ll be creating tables in whatever database you were last pointed at: an easy beginner mistake.

Step 2: Creating a Table (DDL)

CREATE TABLE stores (
    store_id INT,
    store_name VARCHAR(200)
);

This defines the shape of our data before any data exists: two columns, store_id as a whole number and store_name as text up to 200 characters.

Step 3: Inserting Data (DML)

Now that the shelf exists, we can put something on it:

INSERT INTO stores
VALUES 
(1, 'STORE XYZ'),
(2, 'STORE ABC');

This is DML in action: no structure is changing, only the data inside it.

Modifying a Table Later: The ALTER Command (DDL)

Tables rarely stay perfect on the first try. Say I forgot to track where each store is located:

ALTER TABLE stores
ADD COLUMN store_city VARCHAR(200);

And then later I realize “city” was too narrow a name for what I actually wanted to store:

ALTER TABLE stores
RENAME COLUMN store_city TO store_location;

ALTER TABLE is how you evolve a table’s structure without dropping and rebuilding the whole thing from scratch.

Constraints: Rules That Protect Your Data

Constraints are conditions attached to columns that stop bad data from ever getting in. Here’s each one, with a meaningful example rather than just a definition.

NOT NULL: a value must always be provided

CREATE TABLE employees (
    emp_id INT NOT NULL,
    emp_name VARCHAR(100) NOT NULL
);

Without NOT NULL, someone could insert a row with a completely empty name. This constraint refuses to let that happen.

UNIQUE: no two rows can share this value

CREATE TABLE employees (
    emp_id INT,
    email VARCHAR(150) UNIQUE
);

Two employees can’t share the same email address. Interestingly (and this tripped me up at first), a UNIQUE column can still contain a NULL, and in most databases you can even have multiple NULLs, because NULL is treated as “unknown,” not as a duplicate value.

PRIMARY KEY: uniquely identifies every row

CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    emp_name VARCHAR(100)
);

A primary key combines UNIQUE and NOT NULL in one rule. Every row must have one, and no two rows can share it. This is how the database tells rows apart from each other, even if every other column happened to be identical.

CHECK: data must satisfy a condition

CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    age INT CHECK (age >= 18)
);

This physically prevents inserting an employee with an age below 18: the database rejects the row rather than silently accepting bad data.

DEFAULT: fill in a value automatically if none is given

CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    country VARCHAR(50) DEFAULT 'Nepal'
);

If an INSERT doesn’t specify country, MySQL fills it in with 'Nepal' automatically instead of leaving it blank.

Dropping a Constraint

Constraints aren’t permanent. For example, to remove a check constraint later:

ALTER TABLE employees
DROP CHECK employees_chk_1;

(The constraint name varies: MySQL auto-generates one unless you name it explicitly when creating it.)

TRUNCATE: wiping data without touching structure

Worth mentioning alongside constraints, since it’s a DDL command people often confuse with DELETE:

TRUNCATE TABLE employees;

This removes all rows instantly and resets things like auto-increment counters, but keeps the table structure fully intact. It’s faster than DELETE but can’t be filtered with a WHERE clause: it’s all or nothing.

Key Concepts: The Part That Finally Clicked for Me

This is the section I rewrote the most in my notes, because the terms sound interchangeable until you see them side by side.

Unique Key

A column (or set of columns) where every value must be different, but NULL values are still allowed:

email VARCHAR(150) UNIQUE

Primary Key

A column that uniquely identifies each row and never accepts NULL:

emp_id INT PRIMARY KEY

Every table should have exactly one of these: it’s the row’s fingerprint.

Foreign Key

A column that is the primary key of another table, used to link two tables together:

CREATE TABLE store_employees (
    emp_id INT,
    store_id INT,
    FOREIGN KEY (store_id) REFERENCES stores(store_id)
);

Here, store_id in store_employees points back to store_id in stores. This is the mechanism that lets relational databases actually be relational: tables referencing each other instead of duplicating data everywhere.

Candidate Key

Any column (or combination of columns) that could have been chosen as the primary key, because it can uniquely identify a row on its own. A table might have several candidate keys (emp_id and email could both qualify), but only one gets promoted to PRIMARY KEY.

Composite Key

A primary key made of two or more columns together, used when no single column is unique on its own:

CREATE TABLE store_employees (
    emp_id INT,
    store_id INT,
    PRIMARY KEY (emp_id, store_id)
);

Neither emp_id nor store_id alone is guaranteed unique here (an employee could theoretically appear linked to multiple stores), but the combination of the two is.

Surrogate Key

An artificial identifier with no real-world meaning, usually just an auto-incrementing number, created purely so every row has something clean and guaranteed-unique to reference:

CREATE TABLE employees (
    emp_id INT AUTO_INCREMENT PRIMARY KEY,
    emp_name VARCHAR(100)
);

emp_id here doesn’t mean anything on its own: it’s not a name, a date, or anything from the real world. It exists purely as a stable handle for the database to point to.

What I Take Away From Part 1

The biggest shift for me was realizing constraints and keys aren’t just syntax to memorize: they’re the database’s way of enforcing the rules of reality onto raw data. A primary key isn’t a technical requirement for its own sake; it’s the database’s way of guaranteeing “this row is one specific thing, and nothing else is exactly it.”

Coming Up in Part 2

Next up: DQL (Data Query Language): actually querying data with SELECT, filtering with WHERE, sorting, and eventually joins, which is where foreign keys stop being an abstract idea and start actually proving useful.

---

Writing this as a tutorial forced me to explain concepts instead of just listing them, which, honestly, is where most of the real learning happened.