So far we have used company_db as a ready-made database loaded from a script. In this chapter we open that script up and build the database ourselves, one statement at a time.
You will learn the two most basic DDL (Data Definition Language) commands: CREATE DATABASE, which makes an empty container, and CREATE TABLE, which defines the columns, data types and rules of each table. Along the way you will see the errors MySQL throws when something is wrong — and why the order in which you create tables matters.
By the end of the chapter you will have rebuilt the four empty company_db tables. In the next chapter, INSERT INTO, we will fill them with data.
Server, Database, Table: The Hierarchy
One MySQL server can hold many databases, and each database holds many tables:
MySQL server
├── company_db ← a database (a container / namespace)
│ ├── departments ← tables
│ ├── employees
│ ├── customers
│ └── orders
├── college_db
│ └── students ...
└── mysql, sys, ... ← built-in databases (don't touch)A database groups related tables, typically one database per application. Two different databases can each have a table called users without conflict, because the full name is database.table — for example company_db.employees.
Note: In MySQL,
DATABASEandSCHEMAmean the same thing.CREATE SCHEMA company_db;is identical toCREATE DATABASE company_db;. (In PostgreSQL and Oracle, a schema is a different, smaller concept, so don't carry this over.)
CREATE DATABASE
The simplest form is:
CREATE DATABASE database_name;You already loaded company_db in Setting Up MySQL, so if you try to create it again:
CREATE DATABASE company_db;ERROR 1007 (HY000): Can't create database 'company_db'; database existsNames must be unique on a server. In scripts, you usually don't want this to be an error, so use IF NOT EXISTS:
CREATE DATABASE IF NOT EXISTS company_db;
SHOW WARNINGS;+-------+------+-----------------------------------------------------+
| Level | Code | Message |
+-------+------+-----------------------------------------------------+
| Note | 1007 | Can't create database 'company_db'; database exists |
+-------+------+-----------------------------------------------------+Now the statement succeeds and does nothing; the "error" is downgraded to a Note. The existing database and its data are not touched.
Character Set and Collation
Every database has a default character set (how text is encoded into bytes) and collation (how text is compared and sorted). Let us see what MySQL chose for us:
SHOW CREATE DATABASE company_db;+------------+--------------------------------------------------------------------------------------------------------------------------------------+
| Database | Create Database |
+------------+--------------------------------------------------------------------------------------------------------------------------------------+
| company_db | CREATE DATABASE `company_db` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+------------+--------------------------------------------------------------------------------------------------------------------------------------+The /*!40100 ... */ parts are special MySQL comments that older versions ignore. Inside them we see the MySQL 8 defaults:
utf8mb4– full Unicode, including Indian scripts and emojis (see SQL Data Types).utf8mb4_0900_ai_ci– a collation that is accent-insensitive and case-insensitive, so'aarav' = 'Aarav'is true.
These defaults are good. If you want to be explicit (useful on older servers whose defaults were latin1), write:
CREATE DATABASE IF NOT EXISTS company_db
CHARACTER SET utf8mb4
COLLATE utf8mb4_0900_ai_ci;Using and Dropping a Database
SHOW DATABASES; -- list all databases
USE company_db; -- make it the current database
SELECT DATABASE(); -- which database am I in?To delete a whole database — every table and every row in it — there is DROP DATABASE:
DROP DATABASE IF EXISTS company_db;Warning:
DROP DATABASEhas no confirmation prompt and no undo. On a real system, take a backup first.DROPis covered fully in ALTER, DROP & TRUNCATE.
Naming Rules for Databases, Tables and Columns
Names can contain letters, digits,
_and$, and can be up to 64 characters long.Use lowercase snake_case:
order_items,hire_date. Remember from Introduction to SQL that table names are case-sensitive on Linux, so lowercase everywhere avoids surprises.Avoid spaces and SQL keywords (
order,group,select,desc,key...). If you really must use one, wrap it in backticks:`order`.Be consistent. A common convention is plural table names (
employees) and an id column namedtablename_idoremp_id.
Watch what happens with a reserved word:
CREATE TABLE order (id INT);ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'order (id INT)' at line 1ORDER is a keyword (as in ORDER BY), so the parser gets confused. Backticks would make it work (CREATE TABLE `order` (...)), but then you must write backticks in every query that uses it, forever. This is exactly why our table is called orders.
CREATE TABLE: The Syntax
CREATE TABLE table_name (
column1 data_type [column options],
column2 data_type [column options],
...
[table-level constraints]
);Each column needs a name and a data type. After the type you can add optional column options — rules that MySQL enforces on every row:
Option | Meaning |
|---|---|
| The column must always have a value. Without it, the column may be |
| Value used when an |
| Uniquely identifies each row. Implies |
| No two rows can have the same value (but multiple |
| MySQL generates the next number automatically (1, 2, 3 ...). Used for id columns. |
| Value must exist in another table's key column (written as a table-level constraint). |
| Every row must satisfy the condition, e.g. |
These rules are called constraints. In this chapter we use them in their simplest form; the chapter SQL Constraints covers each one in depth — naming them, composite keys, ON DELETE actions and more.
Building company_db Step by Step
Let us rebuild the sample database from scratch. Because you can always reload it with the script from Setting Up MySQL, it is safe to drop it first:
DROP DATABASE IF EXISTS company_db;
CREATE DATABASE company_db;
USE company_db;Table 1: departments
CREATE TABLE departments (
dept_id INT PRIMARY KEY,
dept_name VARCHAR(50) NOT NULL UNIQUE,
location VARCHAR(50) NOT NULL
);Line by line:
dept_id INT PRIMARY KEY– every department has a unique numeric id. MySQL will reject a second department with id 10, and will reject a department with no id.dept_name VARCHAR(50) NOT NULL UNIQUE– every department must have a name, and two departments cannot share a name.location VARCHAR(50) NOT NULL– required, but duplicates are fine (two departments can be in the same city).
Notice that columns are separated by commas, and the last column has no comma after it.
Table 2: employees
CREATE TABLE employees (
emp_id INT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
email VARCHAR(100) UNIQUE,
dept_id INT,
manager_id INT,
salary DECIMAL(10,2) NOT NULL,
hire_date DATE NOT NULL,
city VARCHAR(50),
FOREIGN KEY (dept_id) REFERENCES departments(dept_id),
FOREIGN KEY (manager_id) REFERENCES employees(emp_id)
);The new ideas here:
email VARCHAR(100) UNIQUEwithoutNOT NULL– an employee may have no email (NULL), but no two employees can share one.dept_id INTis nullable – a newly joined employee might not be assigned to a department yet.FOREIGN KEY (dept_id) REFERENCES departments(dept_id)– an employee'sdept_idmust beNULLor adept_idthat really exists indepartments. This keeps the data consistent: no employee can belong to department 99 if there is no department 99.FOREIGN KEY (manager_id) REFERENCES employees(emp_id)– a self-reference. A manager is also an employee, somanager_idmust point to an existingemp_idin the same table. The CEO has no manager, so theirmanager_idisNULL.
Order Matters: Parents Before Children
What if we had tried to create employees before departments?
ERROR 1824 (HY000): Failed to open the referenced table 'departments'A foreign key can only point to a table that already exists. So the table being referred to (the parent, here departments) must be created before the table that refers to it (the child, employees). The self-reference in employees is fine, because the table refers to itself.
The same rule decides the order when deleting: you cannot drop a parent table while a child still references it. That is why scripts drop tables in the reverse order of creation.
Tables 3 and 4: customers and orders
CREATE TABLE customers (
customer_id INT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
city VARCHAR(50),
signup_date DATE NOT NULL
);
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
order_date DATE NOT NULL,
amount DECIMAL(10,2) NOT NULL,
status VARCHAR(20) NOT NULL,
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);customers is the parent, so it comes first. orders.customer_id is nullable on purpose: it allows guest orders that are not linked to any customer.
The resulting relationships look like this:
departments (dept_id PK) customers (customer_id PK)
▲ ▲
│ dept_id FK │ customer_id FK
│ │
employees (emp_id PK) ◄─┐ orders (order_id PK)
│ manager_id FK ──┘Inspecting What You Created
SHOW TABLES
SHOW TABLES;+----------------------+
| Tables_in_company_db |
+----------------------+
| customers |
| departments |
| employees |
| orders |
+----------------------+DESCRIBE
DESCRIBE employees;+------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+-------+
| emp_id | int | NO | PRI | NULL | |
| name | varchar(50) | NO | | NULL | |
| email | varchar(100) | YES | UNI | NULL | |
| dept_id | int | YES | MUL | NULL | |
| manager_id | int | YES | MUL | NULL | |
| salary | decimal(10,2) | NO | | NULL | |
| hire_date | date | NO | | NULL | |
| city | varchar(50) | YES | | NULL | |
+------------+---------------+------+-----+---------+-------+Column | How to read it |
|---|---|
|
|
|
|
| The value used when an insert leaves the column out. |
| Things like |
SHOW CREATE TABLE
DESCRIBE does not show foreign keys. To see the complete definition, ask MySQL to print the CREATE TABLE statement it has stored. Ending the statement with \G instead of ; prints the result vertically, which is easier to read for long values:
SHOW CREATE TABLE employees\G*************************** 1. row ***************************
Table: employees
Create Table: CREATE TABLE `employees` (
`emp_id` int NOT NULL,
`name` varchar(50) NOT NULL,
`email` varchar(100) DEFAULT NULL,
`dept_id` int DEFAULT NULL,
`manager_id` int DEFAULT NULL,
`salary` decimal(10,2) NOT NULL,
`hire_date` date NOT NULL,
`city` varchar(50) DEFAULT NULL,
PRIMARY KEY (`emp_id`),
UNIQUE KEY `email` (`email`),
KEY `dept_id` (`dept_id`),
KEY `manager_id` (`manager_id`),
CONSTRAINT `employees_ibfk_1` FOREIGN KEY (`dept_id`) REFERENCES `departments` (`dept_id`),
CONSTRAINT `employees_ibfk_2` FOREIGN KEY (`manager_id`) REFERENCES `employees` (`emp_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ciThis reveals several things MySQL did for us:
emp_idbecameNOT NULLautomatically because it is the primary key.Nullable columns got
DEFAULT NULL.The unique and foreign-key columns got indexes (
UNIQUE KEY,KEY).Our unnamed foreign keys were given generated names:
employees_ibfk_1andemployees_ibfk_2("ibfk" = InnoDB foreign key). You will see these names in error messages.ENGINE=InnoDB– the default storage engine, which supports transactions and foreign keys.
SHOW CREATE TABLE is also the quickest way to copy a table definition from one server to another.
CREATE TABLE IF NOT EXISTS
Like databases, a table name must be unique within its database:
CREATE TABLE departments (dept_id INT PRIMARY KEY);ERROR 1050 (42S01): Table 'departments' already existsCREATE TABLE IF NOT EXISTS departments (dept_id INT PRIMARY KEY);
SHOW WARNINGS;+-------+------+------------------------------------+
| Level | Code | Message |
+-------+------+------------------------------------+
| Note | 1050 | Table 'departments' already exists |
+-------+------+------------------------------------+Careful:
IF NOT EXISTSonly checks the name. It does not compare definitions. Our existingdepartmentstable still has its three columns — MySQL did not "update" it to the one-column version. To change an existing table's structure, useALTER TABLE(see ALTER, DROP & TRUNCATE).
AUTO_INCREMENT and DEFAULT in Action
In company_db we chose all the id values ourselves. In most real applications, the database generates ids. Let us create one more practice table that we will use in the next chapter:
CREATE TABLE projects (
project_id INT AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(100) NOT NULL UNIQUE,
dept_id INT,
budget DECIMAL(12,2) NOT NULL DEFAULT 0,
status VARCHAR(20) NOT NULL DEFAULT 'Planned',
start_date DATE,
FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
);
DESCRIBE projects;+------------+---------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+----------------+
| project_id | int | NO | PRI | NULL | auto_increment |
| title | varchar(100) | NO | UNI | NULL | |
| dept_id | int | YES | MUL | NULL | |
| budget | decimal(12,2) | NO | | 0.00 | |
| status | varchar(20) | NO | | Planned | |
| start_date | date | YES | | NULL | |
+------------+---------------+------+-----+---------+----------------+project_idshowsauto_increment: if an insert does not give aproject_id, MySQL uses the next number. AnAUTO_INCREMENTcolumn must be part of a key (usually the primary key), and a table can have only one.budgetdefaults to0.00andstatusto'Planned', so new projects get sensible values without the application having to remember them.
Keep this table for now — INSERT INTO uses it to show how these defaults behave, and removes it at the end.
Copying a Table's Structure: CREATE TABLE ... LIKE
Sometimes you need an empty table with exactly the same structure as an existing one — for a backup, an archive or a test:
CREATE TABLE emp_copy LIKE employees;
SHOW CREATE TABLE emp_copy\G*************************** 1. row ***************************
Table: emp_copy
Create Table: CREATE TABLE `emp_copy` (
`emp_id` int NOT NULL,
`name` varchar(50) NOT NULL,
`email` varchar(100) DEFAULT NULL,
`dept_id` int DEFAULT NULL,
`manager_id` int DEFAULT NULL,
`salary` decimal(10,2) NOT NULL,
`hire_date` date NOT NULL,
`city` varchar(50) DEFAULT NULL,
PRIMARY KEY (`emp_id`),
UNIQUE KEY `email` (`email`),
KEY `dept_id` (`dept_id`),
KEY `manager_id` (`manager_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ciCompare it with the original: columns, types, the primary key and indexes are all copied, but the two CONSTRAINT ... FOREIGN KEY lines are missing. CREATE TABLE ... LIKE does not copy foreign keys, and it copies no rows. (There is a second way to copy a table, CREATE TABLE ... AS SELECT, which copies rows but even less structure. We will meet it in INSERT INTO, once our tables have data.)
Clean up the copy with DROP TABLE emp_copy;.
Temporary Tables
CREATE TEMPORARY TABLE scratch (n INT);A temporary table exists only for your current connection. Other users cannot see it, it does not appear in SHOW TABLES, and MySQL drops it automatically when you disconnect. It is useful for storing intermediate results in a long report or script.
Errors You Will Meet in CREATE TABLE
Mistake | Error |
|---|---|
Comma after the last column: |
|
Same column name twice |
|
|
|
A default that does not match the type: |
|
Foreign key to a table that does not exist yet |
|
Foreign key column type differs from the referenced column (e.g. |
|
Reserved word as a name without backticks |
|
Note the "two primary keys" case: a table can have only one primary key, but that key can span several columns. You write such a composite key as a table-level constraint, PRIMARY KEY (student_id, course_id). More on that in SQL Constraints.
Common Mistakes
Creating child tables before parent tables – always create the referenced table first, and drop in reverse order.
Forgetting
USE– tables get created in whatever database is current, or you getERROR 1046: No database selected.Expecting
IF NOT EXISTSto update a table – it silently keeps the old definition.Making every column nullable – if a value is required (name, salary, order date), say
NOT NULL. Rules in the table protect the data from every application that writes to it, including future ones.Using a different type for a foreign key than for the column it references.
Naming tables or columns with keywords or spaces (
order,desc,Order Details) – you will be typing backticks forever.
Interview Questions
What is the difference between a database and a table?
A database is a named container that groups related tables (and other objects like views). A table stores the actual data as rows and columns. In MySQL, a database is also called a schema.
Can a table have more than one primary key?
No. A table has at most one primary key, but it can be a composite key made of several columns, e.g. PRIMARY KEY (student_id, course_id). A table can have many UNIQUE constraints.
What is the difference between PRIMARY KEY and UNIQUE?
Both prevent duplicate values. A primary key also forbids NULL and there is only one per table; a UNIQUE column allows NULL (in MySQL, multiple NULLs) and a table can have many.
Why must the parent table be created before the child table?
A foreign key references a column in another table, and MySQL checks that this table and column exist when the constraint is created. So the referenced (parent) table must already exist.
How do you copy only the structure of a table?
CREATE TABLE new_table LIKE old_table; copies columns, types, indexes and the primary key, but no rows and no foreign keys.
How can you see the full definition of an existing table?
SHOW CREATE TABLE table_name; prints the complete CREATE TABLE statement, including indexes, foreign keys, engine and character set. DESCRIBE shows only the columns.
What does AUTO_INCREMENT do?
It makes MySQL generate the next integer for the column when a row is inserted without a value. It must be on an indexed (usually primary key) column, and only one per table is allowed.
The Main Idea
CREATE DATABASE [IF NOT EXISTS] name [CHARACTER SET utf8mb4];
USE name;
CREATE TABLE [IF NOT EXISTS] name (
column TYPE [NOT NULL] [DEFAULT x] [AUTO_INCREMENT] [UNIQUE] [PRIMARY KEY],
...,
FOREIGN KEY (col) REFERENCES parent(col) ← parent must exist first
);
Inspect → SHOW TABLES; DESCRIBE t; SHOW CREATE TABLE t\G
Copy → CREATE TABLE copy LIKE t; (structure + indexes, no FKs, no rows)
Temporary → CREATE TEMPORARY TABLE ... (this connection only)
Names → lowercase_snake_case, no keywords, max 64 charactersA table definition is a contract: it fixes which columns exist, what type each holds and which rules every row must follow. Now that company_db's four tables exist (empty) along with the projects practice table, the next chapter, INSERT INTO, fills them with data.