# Create Database Scripts

Build a database in SQL with CREATE DATABASE, CREATE TABLE and ALTER TABLE, including primary keys, default values and foreign key constraints.

Journeyman · Database · MySQL · Updated November 16, 2026

By Gregor Dzierzon

Once you have a database design, you need to implement the database by creating scripts. The ANSI standard includes commands that allow you to build database structures. The commands include:

- `CREATE` creates new objects in the database engine
  - `CREATE DATABASE`
  - `CREATE TABLE`
  - `CREATE VIEW`
- `ALTER` changes existing objects, such as the structure of a table
  - `ALTER TABLE`
  - `ALTER VIEW`
- `DROP` deletes an object from the database
  - `DROP DATABASE`
  - `DROP TABLE`
  - `DROP VIEW`

## Create Database

Start by creating a new database to hold the tables.

```sql
-- before creating a database you need to ensure that one
-- with the same name does not already exist
DROP DATABASE IF EXISTS northwind;

-- this creates a new empty database
CREATE DATABASE northwind;

-- switch context to the new database
USE northwind;
```

## Create Tables

The CREATE TABLE command lets you create a new table structure. You must specify the column names, types and whether the column is required.

### Create Table Syntax

```sql
CREATE TABLE table_name
(
    column_1 datatype [constraints],
    column_2 datatype [constraints],
    table_constraints
);
```

### Example

Create the `categories` and `products` tables.

```sql
CREATE TABLE categories
(
    category_id INT NOT NULL AUTO_INCREMENT,
    category_name VARCHAR(15) NOT NULL,
    description TEXT,
    PRIMARY KEY (category_id)
);

CREATE TABLE products
(
    product_id INT NOT NULL AUTO_INCREMENT,
    product_name VARCHAR(40) NOT NULL,
    supplier_id INT NULL,
    category_id INT NULL,
    quantity_per_unit VARCHAR(20) NULL,
    unit_price NUMERIC(10,2) NOT NULL,
    units_in_stock INT NOT NULL DEFAULT 0,
    units_on_order INT NOT NULL DEFAULT 0,
    reorder_level INT NOT NULL DEFAULT 0,
    discontinued BOOLEAN NOT NULL DEFAULT false,
    PRIMARY KEY (product_id)
);
```

Note that when creating the `products` table, the script also provides logic to add default values to several of the columns. These defaults will be used when values are not provided.

## Add Foreign Key Constraints

Foreign Key constraints are the relationships that link 2 tables together. A FK is created in the child table and links to the parent table. For example, the child table `products` has a foreign key relationship to its parent table `categories`.

A FK can be added when the table is created, but it is common to create the relationships after all tables are created as follows.

```sql
ALTER TABLE products
ADD CONSTRAINT fk_products_categories
FOREIGN KEY (category_id) REFERENCES categories (category_id);
```

## Do it: create the shippers table so bad rows stay out

Northwind needs one more table: `shippers`, the shipping companies an order can go with. The office manager wants the table itself to turn away bad entries, so a typo in any app can't get in.

Its four columns:

- `shipper_id`: the shipper's number, an `INT`, never empty. It's the primary key.
- `company_name`: the company's name, `VARCHAR(40)`, never empty.
- `phone`: the company's phone number, `VARCHAR(24)`. It may be empty (`NULL`).
- `active`: whether Northwind still ships with the company, a `BOOLEAN`, never empty. When an `INSERT` leaves it out, it's `true`.

In MySQL, `shipper_id` would be an `AUTO_INCREMENT` column. The practice box runs SQLite, which writes that differently, so leave that word out: the check gives each id itself. Leave out `DROP DATABASE`, `CREATE DATABASE` and `USE` too; the box starts with a fresh, empty database.

After your script runs, the check tries to add these five shippers, one `INSERT` at a time. A row that breaks one of your table's rules is turned away, and the rest are kept:

| shipper_id | company_name | phone | active | What happens |
|---|---|---|---|---|
| 1 | Speedy Express | (503) 555-9831 | *(left out)* | Kept, with `active` true |
| 2 | United Package | `NULL` | *(left out)* | Kept: a phone number isn't required |
| 3 | `NULL` | (503) 555-9931 | true | Turned away: the name is empty |
| 1 | Federal Shipping | (503) 555-9931 | true | Turned away: `shipper_id` 1 is taken |
| 4 | Federal Shipping | (503) 555-9931 | false | Kept |

Then it reads the table with `SELECT shipper_id, company_name, phone, active FROM shippers ORDER BY shipper_id;`, which should give exactly these rows (SQLite shows `true` as `1` and `false` as `0`):

| shipper_id | company_name | phone | active |
|---|---|---|---|
| 1 | Speedy Express | (503) 555-9831 | 1 |
| 2 | United Package | NULL | 1 |
| 4 | Federal Shipping | (503) 555-9931 | 0 |

**Your job:** write one `CREATE TABLE shippers` statement.

- The four columns above, with their types.
- `NOT NULL` on every column that may never be empty, and not on `phone`.
- A `DEFAULT` of `true` for `active`.
- A primary key on `shipper_id`.
- Only the table: no `INSERT`s of your own, because the check adds the rows.

Practiced live on the lesson page: write the SQL right there and check it.

## Cheat sheets

- [Create Database Scripts](https://www.ask-codey.com/cheat-sheets/foundations-of-database-design-create-database-scripts-cheat-sheet)

Source: https://www.ask-codey.com/lessons/foundations-of-database-design-create-database-scripts
