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:

sql

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:

sql
CREATE TABLE

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:

sql
CREATE TABLE

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:

sql
CREATE TABLE

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:

sql
CREATE TABLE

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:

sql
column_name    | data_type          | is_nullable | column_default
โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€
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

sql
ALTER TABLE

This adds a new loyalty_points column to the existing customers table. All existing rows get 0 points by default.

Q7: Rename a Column

sql
ALTER TABLE

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)

sql
DROP TABLE

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:

text

Download Complete Schema Script

Save time by downloading a ready-to-run SQL script that creates all four tables:

๐Ÿ“ฅ Download quickcart-schema.sql

To run it: psql quickcart -f quickcart-schema.sql

Challenge

Create a new table called reviews with these columns:

  • review_id โ€” Auto-incrementing primary key
  • product_id โ€” Foreign key to products
  • customer_id โ€” Foreign key to customers
  • rating โ€” Integer between 1 and 5 (use CHECK constraint)
  • review_text โ€” Text, optional
  • review_date โ€” Date, default to today

Then run a query to verify the table was created by checking information_schema.

Show hint

Here's the structure to follow:

sql

Test Your Knowledge

Question 1 of 5
What does a PRIMARY KEY constraint do?
Question 2 of 5
What does a FOREIGN KEY constraint ensure?
Question 3 of 5
What does the CHECK constraint do?
Question 4 of 5
What does DEFAULT do in a column definition?
Question 5 of 5
When dropping multiple related tables, what order should you use?
0
/5
Africa & India's Best AI Academy
๐Ÿค– AI-first ๐ŸŽ“ Placement Assistance

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 โ†’