What is a Table?
A table is the fundamental building block of any relational database. It's a structured collection of data organized in rows and columns. Think of it like a spreadsheet: each row is a record, and each column defines the type of data it holds.
In this lesson, you'll learn how to create tables with constraints that enforce data integrity. Constraints are rules that ensure your data stays valid and consistent. By the end, you'll design a complete e-commerce schema for QuickCart with four related tables: customers, products, orders, and order_items.
CREATE TABLE Syntax
Here's the basic structure of a CREATE TABLE statement:
Each column has a name, a data type (like VARCHAR, INTEGER, DATE), and optional constraints that define rules for that column.
Understanding Constraints
Constraints are rules that guarantee data quality. Here are the most important ones:
NOT NULL โ The column must always have a value. No empty cells allowed.
UNIQUE โ Each value in the column must be different. No duplicates allowed (except NULL).
PRIMARY KEY โ A special constraint combining NOT NULL + UNIQUE. Uniquely identifies each row. Every table should have exactly one PRIMARY KEY.
FOREIGN KEY โ Links this table to another. Ensures referential integrity. For example, order.customer_id must match a customer_id in the customers table.
CHECK โ Custom rule. Example: CHECK (price > 0) ensures price is always positive.
DEFAULT โ If no value is provided, use this default. Example: DEFAULT CURRENT_DATE records today's date automatically.
Q1: Create the Customers Table
Let's start building the QuickCart schema. First, we'll create a customers table to store customer information:
Breakdown:
customer_id SERIAL PRIMARY KEYโ Auto-incrementing unique ID (the primary key).first_name VARCHAR(50) NOT NULLโ Text up to 50 characters, required.email VARCHAR(100) UNIQUE NOT NULLโ Email must be unique and required.phone VARCHAR(15)โ Optional phone number.signup_date DATE DEFAULT CURRENT_DATEโ Automatically records today's date when no date is provided.is_premium BOOLEAN DEFAULT FALSEโ True/False flag, defaults to FALSE.
Q2: Create the Products Table
Next, we'll create a products table with price validation:
Breakdown:
price DECIMAL(10,2) CHECK (price > 0)โ Stores money as decimal with 10 digits (8 before decimal, 2 after). CHECK ensures price is always positive.stock_qty INTEGER CHECK (stock_qty >= 0)โ Can't have negative inventory.TIMESTAMP DEFAULT NOW()โ Records the exact creation time (date and time).
Q3: Create the Orders Table (with Foreign Key)
The orders table links to customers. Notice the FOREIGN KEY constraint:
Breakdown:
customer_id INTEGER REFERENCES customers(customer_id)โ Foreign key linking to customers. Each order must belong to an existing customer.status VARCHAR(20) CHECK (status IN (...))โ CHECK ensures status is one of the allowed values only.payment_method VARCHAR(20) CHECK (...)โ Restricts payment methods to these four options.
Q4: Create the Order Items Table
The order_items table stores individual items in each order. It has two foreign keys:
Breakdown:
REFERENCES orders(order_id) ON DELETE CASCADEโ If an order is deleted, automatically delete all its items. This maintains data consistency.quantity INTEGER CHECK (quantity > 0)โ Can't order zero items.discount DECIMAL(5,2) CHECK (discount >= 0 AND discount <= 100)โ Discount is between 0 and 100 percent.
Q5: View Table Structure with information_schema
Want to see all the columns and constraints you just created? PostgreSQL stores this metadata in the information_schema:
โโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโ
customer_id | integer | NO | nextval('customers_customer_id_seq'::regclass)
first_name | character varying | NO | null
last_name | character varying | NO | null
email | character varying | NO | null
phone | character varying | YES | null
city | character varying | NO | null
signup_date | date | YES | CURRENT_DATE
is_premium | boolean | YES | false
What this shows:
- column_name โ The name of each column.
- data_type โ What kind of data it holds (integer, character varying, date, boolean).
- is_nullable โ YES if it can be NULL (empty), NO if NOT NULL constraint is on it.
- column_default โ The default value if you don't provide one.
Modifying Tables with ALTER TABLE
Once a table is created, you might need to add a column or rename one. That's where ALTER TABLE comes in:
Q6: Add a Column
This adds a new loyalty_points column to the existing customers table. All existing rows get 0 points by default.
Q7: Rename a Column
Renames the supplier column to supplier_name for clarity. All data is preserved.
Note: Renaming a column may break code that references it. Always update your queries and application code to use the new name.
Dropping Tables Safely
To delete a table, use DROP TABLE. Always use IF EXISTS to avoid errors, and drop tables in the right order to respect foreign key constraints:
Q8: Drop Tables (Safe Order)
Why this order?
- Drop order_items first (it references orders and products).
- Then drop orders (it references customers).
- Then drop products (no dependencies).
- Finally drop customers (no dependencies).
If you try to drop customers first, PostgreSQL will refuse because orders depends on it. Always respect the dependency chain!
WARNING: DROP TABLE permanently deletes the table and all its data. Always back up first, and be certain before you run this command.
The Complete QuickCart Schema
Here's a summary of all four tables and their relationships:
Download Complete Schema Script
Save time by downloading a ready-to-run SQL script that creates all four tables:
๐ฅ Download quickcart-schema.sqlTo run it: psql quickcart -f quickcart-schema.sql
Challenge
Create a new table called reviews with these columns:
review_idโ Auto-incrementing primary keyproduct_idโ Foreign key to productscustomer_idโ Foreign key to customersratingโ Integer between 1 and 5 (use CHECK constraint)review_textโ Text, optionalreview_dateโ Date, default to today
Then run a query to verify the table was created by checking information_schema.
Here's the structure to follow:
Test Your Knowledge
You're building real database schemas. Go deeper.
Join the Best AI Program: The AI-Era Data Engineer โ 5 months, fully online, with placement assistance. Week 1 is free.
Explore The AI-Era Data Engineer โ