MySQL Create Tables
Now you will create the two tables of the shop database, using MySQL features such as AUTO_INCREMENT.
The products Table
Example
USE shop;
CREATE TABLE products (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
category VARCHAR(50) NOT NULL DEFAULT 'Grocery',
price DECIMAL(10, 2) NOT NULL CHECK (price >= 0),
stock INT UNSIGNED NOT NULL DEFAULT 0,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
UNIQUE (name)
);Query OK, 0 rows affected (0.03 sec)
What each part means
| Part | Meaning |
|---|---|
AUTO_INCREMENT | MySQL gives each new row the next number: 1, 2, 3... |
PRIMARY KEY | The unique ID of each row |
NOT NULL | A value is required |
DEFAULT 'Grocery' | Used when no category is given |
CHECK (price >= 0) | Refuses negative prices (enforced from MySQL 8.0.16) |
UNSIGNED | No negative numbers allowed |
UNIQUE (name) | Two products cannot have the same name |
The orders Table and a Foreign Key
Example
CREATE TABLE orders (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
product_id INT UNSIGNED NOT NULL,
quantity INT UNSIGNED NOT NULL DEFAULT 1,
customer VARCHAR(100) NOT NULL,
order_date DATE NOT NULL,
CONSTRAINT fk_orders_product
FOREIGN KEY (product_id) REFERENCES products(id)
ON DELETE RESTRICT
ON UPDATE CASCADE
);The foreign key makes sure every order points to a real product.
| Option | Effect when a product is deleted or its id changes |
|---|---|
ON DELETE RESTRICT | Refuse to delete a product that has orders |
ON DELETE CASCADE | Delete its orders too |
ON DELETE SET NULL | Set product_id to NULL in its orders |
ON UPDATE CASCADE | If the product id changes, update the orders too |
The column and the column it references must have the same type. Here both are INT UNSIGNED. If one is INT and the other INT UNSIGNED, MySQL refuses to create the foreign key.
Storage Engines
MySQL stores tables using a storage engine. The default, InnoDB, supports foreign keys, transactions and crash recovery. You can name it, but you rarely need to:
Example
CREATE TABLE notes (
id INT AUTO_INCREMENT PRIMARY KEY,
body TEXT
) ENGINE = InnoDB;See Your Tables
Example
SHOW TABLES;+----------------+ | Tables_in_shop | +----------------+ | notes | | orders | | products | +----------------+ 3 rows in set (0.00 sec)
DESCRIBE shows the columns of a table:
Example
DESCRIBE orders;+------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+--------------+------+-----+---------+----------------+ | id | int unsigned | NO | PRI | NULL | auto_increment | | product_id | int unsigned | NO | MUL | NULL | | | quantity | int unsigned | NO | | 1 | | | customer | varchar(100) | NO | | NULL | | | order_date | date | NO | | NULL | | +------------+--------------+------+-----+---------+----------------+ 5 rows in set (0.00 sec)
PRI marks the primary key. MUL means the column has an index that allows repeated values (MySQL adds one for every foreign key).
SHOW CREATE TABLE orders; prints the full CREATE TABLE statement, which is handy for copying a table's design.
Change a Table with ALTER TABLE
Example
-- add a column after another column
ALTER TABLE products ADD COLUMN brand VARCHAR(50) AFTER name;
-- change a column's type
ALTER TABLE products MODIFY COLUMN brand VARCHAR(80);
-- rename a column
ALTER TABLE products RENAME COLUMN brand TO maker;
-- remove a column
ALTER TABLE products DROP COLUMN maker;AFTER and FIRST are MySQL extras that choose where the new column appears.
Remove a Table
Example
DROP TABLE IF EXISTS notes;
TRUNCATE TABLE orders;DROP TABLE deletes the table completely. TRUNCATE TABLE empties it and resets the AUTO_INCREMENT counter back to 1.
Exercise
Exercise
What does AUTO_INCREMENT do on the id column?