Chapter 04 of 35

CREATE DATABASE & CREATE TABLE

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, DATABASE and SCHEMA mean the same thing. CREATE SCHEMA company_db; is identical to CREATE 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 exists

Names 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 DATABASE has no confirmation prompt and no undo. On a real system, take a backup first. DROP is 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 named tablename_id or emp_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 1

ORDER 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

NOT NULL

The column must always have a value. Without it, the column may be NULL (unknown/missing).

DEFAULT value

Value used when an INSERT does not provide one.

PRIMARY KEY

Uniquely identifies each row. Implies NOT NULL and unique. Only one per table.

UNIQUE

No two rows can have the same value (but multiple NULLs are allowed).

AUTO_INCREMENT

MySQL generates the next number automatically (1, 2, 3 ...). Used for id columns.

FOREIGN KEY ... REFERENCES

Value must exist in another table's key column (written as a table-level constraint).

CHECK (condition)

Every row must satisfy the condition, e.g. CHECK (salary > 0).

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) UNIQUE without NOT NULL – an employee may have no email (NULL), but no two employees can share one.

  • dept_id INT is nullable – a newly joined employee might not be assigned to a department yet.

  • FOREIGN KEY (dept_id) REFERENCES departments(dept_id) – an employee's dept_id must be NULL or a dept_id that really exists in departments. 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, so manager_id must point to an existing emp_id in the same table. The CEO has no manager, so their manager_id is NULL.

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

Null

NO = the column is NOT NULL; YES = NULL is allowed.

Key

PRI = primary key; UNI = unique; MUL = a non-unique index, where the same value can appear multiple times. MySQL automatically creates such an index on every foreign key column, which is why dept_id and manager_id show MUL.

Default

The value used when an insert leaves the column out. NULL here simply means "no default defined".

Extra

Things like auto_increment.

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_ci

This reveals several things MySQL did for us:

  • emp_id became NOT NULL automatically 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_1 and employees_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 exists
CREATE TABLE IF NOT EXISTS departments (dept_id INT PRIMARY KEY);
SHOW WARNINGS;
+-------+------+------------------------------------+
| Level | Code | Message                            |
+-------+------+------------------------------------+
| Note  | 1050 | Table 'departments' already exists |
+-------+------+------------------------------------+

Careful: IF NOT EXISTS only checks the name. It does not compare definitions. Our existing departments table still has its three columns — MySQL did not "update" it to the one-column version. To change an existing table's structure, use ALTER 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_id shows auto_increment: if an insert does not give a project_id, MySQL uses the next number. An AUTO_INCREMENT column must be part of a key (usually the primary key), and a table can have only one.

  • budget defaults to 0.00 and status to '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_ci

Compare 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: (id INT PRIMARY KEY, total DECIMAL(10,2), )

ERROR 1064 (42000): ... near ')' at line 1

Same column name twice

ERROR 1060 (42S21): Duplicate column name 'id'

PRIMARY KEY written on two columns separately

ERROR 1068 (42000): Multiple primary key defined

A default that does not match the type: amount DECIMAL(10,2) DEFAULT 'abc'

ERROR 1067 (42000): Invalid default value for 'amount'

Foreign key to a table that does not exist yet

ERROR 1824 (HY000): Failed to open the referenced table 'departments'

Foreign key column type differs from the referenced column (e.g. VARCHAR → INT)

ERROR 3780 (HY000): Referencing column 'dept_id' and referenced column 'dept_id' in foreign key constraint 'bad4_ibfk_1' are incompatible.

Reserved word as a name without backticks

ERROR 1064 (42000): ... near 'order (id INT)'

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 get ERROR 1046: No database selected.

  • Expecting IF NOT EXISTS to 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 characters

A 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.