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
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:
CREATEcreates new objects in the database engineCREATE DATABASECREATE TABLECREATE VIEW
ALTERchanges existing objects, such as the structure of a tableALTER TABLEALTER VIEW
DROPdeletes an object from the databaseDROP DATABASEDROP TABLEDROP VIEW
Create Database
Start by creating a new database to hold the tables.
-- 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
CREATE TABLE table_name
(
column_1 datatype [constraints],
column_2 datatype [constraints],
table_constraints
);
Example
Create the categories and products tables.
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.
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, anINT, 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, aBOOLEAN, never empty. When anINSERTleaves it out, it'strue.
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 NULLon every column that may never be empty, and not onphone.- A
DEFAULToftrueforactive. - A primary key on
shipper_id. - Only the table: no
INSERTs of your own, because the check adds the rows.
Practiced live on the lesson page: write the SQL right there and check it.