Creating and Altering Tables

Our marketplace database is already useful, but real systems evolve. New requirements appear - perhaps we need to track warehouses, shipment tracking, or seller payouts. This lesson shows how to create new tables, define constraints, and adapt existing structures safely.

Learning Goals

• Create tables that extend the marketplace schema
• Choose data types and constraints that match commerce data
• Use ALTER TABLE to evolve existing structures
• Recognize when to alter versus rebuild a table

Creating Tables

Start by identifying the columns, data types, and relationships. Suppose operations wants to track warehouses.

Create a warehouse table

CREATE TABLE warehouse (
  warehouse_id   INT AUTO_INCREMENT PRIMARY KEY,
  warehouse_name VARCHAR(120) NOT NULL,
  city           VARCHAR(80) NOT NULL
);

Now link products to warehouses with stock counts:

Create a product_warehouse bridge table

CREATE TABLE product_warehouse (
  product_id   INT NOT NULL,
  warehouse_id INT NOT NULL,
  on_hand      INT NOT NULL CHECK (on_hand >= 0),
  PRIMARY KEY (product_id, warehouse_id),
  FOREIGN KEY (product_id) REFERENCES product(product_id),
  FOREIGN KEY (warehouse_id) REFERENCES warehouse(warehouse_id)
);

This design enforces that every inventory row references valid records on both sides.

Choosing Data Types and Constraints

Relational structure is about trust. Use constraints to assert what must hold true.

Shipment tracking with constraints

CREATE TABLE shipment (
  shipment_id     INT AUTO_INCREMENT PRIMARY KEY,
  order_id        INT NOT NULL,
  carrier         VARCHAR(80) NOT NULL,
  tracking_number VARCHAR(80) NOT NULL UNIQUE,
  shipped_at      DATE NOT NULL,
  estimated_days  INT NOT NULL CHECK (estimated_days > 0),
  FOREIGN KEY (order_id) REFERENCES orders(order_id)
);

• CHECK (estimated_days > 0) guarantees positive transit times.
• tracking_number is unique so a shipment is easy to locate.

Altering Existing Tables

Schemas change. Use ALTER TABLE to evolve them while preserving existing data.

Add a Column

Track customer phone numbers

ALTER TABLE customer
ADD COLUMN phone VARCHAR(30);

New rows can populate phone, while existing rows default to NULL.

Modify a Column

MySQL supports most alteration operations, but large tables can still require careful planning because changes may lock the table.

ALTER TABLE orders
MODIFY COLUMN status VARCHAR(30) DEFAULT 'Pending';

When a change is too disruptive, create a new table, copy data, and rename - most ORMs provide helpers for this.

Drop a Column

Remove optional phone numbers

ALTER TABLE customer
DROP COLUMN phone;

This permanently removes the phone column and its data. Export important values before dropping them.

Renaming and Adding Constraints

Rename columns or tables

ALTER TABLE product RENAME COLUMN product_name TO name;
ALTER TABLE orders RENAME TO customer_order;

Add a constraint after table creation

ALTER TABLE seller
ADD CONSTRAINT store_name_unique UNIQUE (store_name);

Adding constraints later is common when legacy data needs cleanup first. Validate that existing rows obey the new rule before enforcing it.

When to Alter vs Rebuild

• Alter in place for small, backward-compatible changes (add column, rename column/table, add indexes, enforce new uniqueness).
• Rebuild when you drastically change relationships, swap data types that aren't compatible, or need to backfill columns with computed values. In practice you'll:
  1. Create a new table with the desired structure.
  2. Copy data over with INSERT ... SELECT.
  3. Drop the old table and rename the new one.

Always run these steps inside a transaction to protect against partial failures.

Summary

• CREATE TABLE defines columns, data types, defaults, and relationships.
• Constraints (PRIMARY KEY, NOT NULL, UNIQUE, CHECK, FOREIGN KEY) uphold data quality for marketplace records.
• ALTER TABLE adapts existing structures - add columns, rename objects, and enforce new business rules.
• Evaluate impact before altering production tables; plan migrations that keep data safe.

Quiz

Show quiz

1. Which constraint ensures every tracking number is different?
   A) PRIMARY KEY
   B) NOT NULL
   C) UNIQUE
   D) CHECK

2. Why does the product_warehouse table use a composite primary key?
   A) To auto-increment warehouse IDs
   B) To allow duplicate assignments
   C) To prevent the same product-warehouse pair from being stored twice
   D) To improve text search

3. What should you do before dropping a column that may contain important data?
   A) Nothing - dropping preserves values automatically
   B) Create a backup or export of the data
   C) Change its data type to text
   D) Rename the table first

4. True or False: Adding a new column with ALTER TABLE affects existing rows immediately.
   A) True, new rows must supply a value
   B) False, existing rows receive NULL (unless a default is specified)

---

Answers

1. C
2. C
3. B
4. B