Create tables with types and constraints
Use DDL to define a small relational schema and let the database enforce its rules.
Data definition language
Data Definition Language (DDL) changes database structure. CREATE TABLE defines a table, ALTER TABLE changes one, and DROP TABLE removes one. Treat destructive DDL carefully, especially outside a practice database.
The following PostgreSQL schema supports the examples in this course:
CREATE TABLE customers (
customer_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name text NOT NULL,
email text NOT NULL UNIQUE,
created_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE products (
product_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name text NOT NULL,
price numeric(10, 2) NOT NULL CHECK (price >= 0),
active boolean NOT NULL DEFAULT true
);
CREATE TABLE orders (
order_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
customer_id integer NOT NULL REFERENCES customers(customer_id),
ordered_at timestamptz NOT NULL DEFAULT now(),
status text NOT NULL CHECK (status IN ('pending', 'paid', 'cancelled'))
);
CREATE TABLE order_items (
order_id integer NOT NULL REFERENCES orders(order_id),
product_id integer NOT NULL REFERENCES products(product_id),
quantity integer NOT NULL CHECK (quantity > 0),
unit_price numeric(10, 2) NOT NULL CHECK (unit_price >= 0),
PRIMARY KEY (order_id, product_id)
);
Data types and constraints
Choose a type that fits the value. integer works for whole-number IDs and counts; numeric(10, 2) stores decimal prices exactly to two fractional digits; boolean stores true/false; timestamptz represents a timestamp with time-zone handling. PostgreSQL's text is commonly used for variable-length strings.
Constraints make invalid states harder to store:
PRIMARY KEYmakes each row identifiable and non-null.FOREIGN KEYenforces a valid reference to another table.NOT NULLrequires a value.UNIQUEprevents duplicates in a column or column combination.CHECKrequires a Boolean condition to hold.DEFAULTsupplies a value when an insert omits the column.
The identity-column syntax and timestamptz are PostgreSQL-oriented; other DBMSs may use different spellings or semantics. See PostgreSQL table creation and constraints.
Exercise: Add a discount_percent column that permits values from 0 through 100. Which type and constraint would you use?