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:
| Column | Type | Note |
|---|---|---|
id | INT | Unique identifier: no two rows can share it |
name | VARCHAR(255) | String of up to 255 characters |
birthday | DATE | A date |
city | VARCHAR(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'
);
| Keyword | Role |
|---|---|
NOT NULL | The field is required |
AUTO_INCREMENT | MySQL assigns 1, 2, 3… automatically on insert |
PRIMARY KEY | The primary key: each row’s unique identifier |
DEFAULT | The 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 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,UPDATEandDELETEapply 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
| Command | Role |
|---|---|
CREATE TABLE | Create a table |
RENAME TABLE … TO … | Rename a table |
DROP TABLE | Delete a table |
ALTER TABLE … ADD / DROP / MODIFY / CHANGE | Modify columns |
FOREIGN KEY … REFERENCES | Link two tables |
INSERT, UPDATE, DELETE, TRUNCATE | Manipulate 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.