A database without tables is an empty box. In this chapter, we create and modify tables, link them together, then insert, update and delete data.

Anatomy of a table

Take a user table with four columns:

ColumnTypeNote
idINTUnique identifier: no two rows can share it
nameVARCHAR(255)String of up to 255 characters
birthdayDATEA date
cityVARCHAR(255)String

To create a table, you define each field’s name, type and any rules (required, unique, default value…).

Creating a table: CREATE TABLE

CREATE TABLE user (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) DEFAULT NULL,
    birthday DATE NOT NULL,
    city VARCHAR(255) NOT NULL DEFAULT 'Tunis'
);
KeywordRole
NOT NULLThe field is required
AUTO_INCREMENTMySQL assigns 1, 2, 3… automatically on insert
PRIMARY KEYThe primary key: each row’s unique identifier
DEFAULTThe value used when none is given on insert

In phpMyAdmin, open your database, go to the SQL tab, paste the query and run it: the user table appears. You can also create a table straight from the interface, by choosing the number of columns, then each column’s name, type and options.

Renaming and deleting a table

RENAME TABLE user TO utilisateur;

DROP TABLE utilisateur;

Modifying columns: ALTER TABLE

-- Add a column
ALTER TABLE user ADD country VARCHAR(100);

-- Remove a column
ALTER TABLE user DROP COLUMN country;

-- Change a column's type
ALTER TABLE user MODIFY city VARCHAR(100);

-- Rename a column (restating its type)
ALTER TABLE user CHANGE city town VARCHAR(100);

Try them yourself on your table: it’s the best way to remember them.

Relationships between tables

We’re working with a relational database: tables are linked to each other. Two tables are linked through a foreign key (FOREIGN KEY), a column that references the primary key of another table.

One to oneuser11passportOne to manyclient1Ncommandeforeign key on the "many" sideMany to manystudent1NinscriptionN1coursejunction table
The three kinds of relationships, and where the foreign key goes in each case.

One to one

Example: a person has a single passport, and a passport belongs to a single person.

CREATE TABLE user (
    user_id INT PRIMARY KEY,
    first_name VARCHAR(100),
    last_name VARCHAR(100)
);

CREATE TABLE passport (
    user_id INT PRIMARY KEY,
    passport_number VARCHAR(50),
    expiration_date DATE,
    FOREIGN KEY (user_id) REFERENCES user(user_id)
);

Here, user_id is both the primary key and the foreign key of passport: one person can’t have two passports.

A variant: a primary key made of two columns, the user and the passport number. Their combination must be unique:

CREATE TABLE passport (
    user_id INT,
    passport_number VARCHAR(50),
    FOREIGN KEY (user_id) REFERENCES user(user_id),
    PRIMARY KEY (user_id, passport_number)
);

One to many

Example: a customer can place several orders, but an order belongs to a single customer. Read the other way round, it’s many to one.

CREATE TABLE client (
    client_id INT PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE commande (
    commande_id INT PRIMARY KEY,
    client_id INT,
    FOREIGN KEY (client_id) REFERENCES client(client_id)
);

The foreign key goes on the “many” side: each order points to its customer.

Many to many

Example: a student can take several courses, and a course can be taken by several students.

Here, a foreign key in one of the two tables isn’t enough. You need a junction table, with one row per student / course pair:

CREATE TABLE student (
    student_id INT PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE course (
    course_id INT PRIMARY KEY,
    title VARCHAR(100)
);

CREATE TABLE inscription (
    inscription_id INT PRIMARY KEY,
    student_id INT,
    course_id INT,
    FOREIGN KEY (student_id) REFERENCES student(student_id),
    FOREIGN KEY (course_id) REFERENCES course(course_id)
);

Two options for the junction table:

  • it holds only the two foreign keys, and their combination is the primary key;
  • it needs extra information, like an enrollment ID here. It then gets its own primary key.

Constraints (CONSTRAINT)

A constraint is a rule applied to a column or a set of columns, to guarantee the integrity and consistency of the data. You can name them with CONSTRAINT:

CREATE TABLE commande (
    commande_id INT,
    client_id INT,
    CONSTRAINT pk_commande PRIMARY KEY (commande_id),
    CONSTRAINT fk_commande_client FOREIGN KEY (client_id) REFERENCES client(client_id)
);

Both syntaxes (named or not) work. Naming your constraints mostly helps you find and drop them later.

Manipulating data

Insert: INSERT INTO

INSERT INTO user (name, birthday, city)
VALUES ('Safe', '1995-04-12', 'Paris');

Listing the fields you fill avoids nasty surprises: if a country column with DEFAULT NULL is added later, this query keeps working.

Update: UPDATE

UPDATE user
SET birthday = '1995-05-01'
WHERE name = 'Safe';

SET says which column to change, WHERE says which rows. Careful: if 50 rows are named “Safe”, all 50 get updated. To target a single row, use its ID: WHERE id = 3.

Delete: DELETE

DELETE FROM user
WHERE name = 'Safe';

⚠️ Without WHERE, UPDATE and DELETE apply to the whole table. Always double-check your condition before running them.

Empty a table: TRUNCATE

TRUNCATE TABLE user;

TRUNCATE removes all the table’s content at once, like a DELETE without WHERE, and also resets the AUTO_INCREMENT counter.

Recap

CommandRole
CREATE TABLECreate a table
RENAME TABLE … TO …Rename a table
DROP TABLEDelete a table
ALTER TABLE … ADD / DROP / MODIFY / CHANGEModify columns
FOREIGN KEY … REFERENCESLink two tables
INSERT, UPDATE, DELETE, TRUNCATEManipulate data

Exercise · Authors and books

Create two tables: author (id, name) and book (id, title, author_id). An author can write several books. Add the foreign key, then insert one author and two of their books.

Show the solution
CREATE TABLE author (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

CREATE TABLE book (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    author_id INT NOT NULL,
    CONSTRAINT fk_book_author FOREIGN KEY (author_id) REFERENCES author(id)
);

INSERT INTO author (name) VALUES ('Victor Hugo');
INSERT INTO book (title, author_id) VALUES
    ('Les Misérables', 1),
    ('The Hunchback of Notre-Dame', 1);

It’s a one-to-many relationship: the foreign key goes on the “many” side, so in book. Then try inserting a book with author_id = 99: MySQL refuses, since that author doesn’t exist.

What’s Next

Our tables are ready and filled. In the next chapter, we learn to query the data with SELECT.


This is Chapter 4 of the SQL from Zero to Pro course. Follow along to learn SQL with MySQL, from the basics to joins.