Difficulty: Beginner | Prerequisites: Part 1 (Query Foundations) recommended for context, but not strictly required.
Querying data is only half the picture. You also need to build and modify the tables that hold that data. This set of notes covers everything about table creation and alteration: defining columns and data types, enforcing data quality through constraints (PRIMARY KEY, NOT NULL, UNIQUE, FOREIGN KEY, CHECK, DEFAULT), using auto-incrementing keys, and modifying or removing tables after they exist. These skills underpin database design and are essential for understanding why queries behave the way they do.
Tables are created with CREATE TABLE, specifying column names, data types, and constraints. Constraints (PRIMARY KEY, NOT NULL, UNIQUE, FOREIGN KEY, CHECK, DEFAULT) enforce data integrity at the database level. Tables can be modified with ALTER TABLE (add/drop columns, change data types and sizes) and removed with DROP TABLE, but foreign key relationships dictate the order in which tables can be dropped.
CREATE TABLE
The SQL statement that defines a new table, including its name, columns, data types, and constraints.
Data type
The kind of value a column can store. Common types include INT, VARCHAR, TEXT, BOOLEAN, DATE, TIMESTAMP, NUMERIC, SERIAL, and FLOAT.
VARCHAR(n)
A variable-length character string that stores up to n characters. Unlike CHAR(n), it does not pad extra spaces.
CHAR(n)
A fixed-length character string of exactly n characters. Shorter strings are padded with spaces.
TEXT
A variable-length character string with no defined limit. In simple terms, it is VARCHAR without a cap.
INT / INTEGER
A 4-byte whole number with a range of roughly negative 2.1 billion to positive 2.1 billion.
SMALLINT
A 2-byte whole number with a range of negative 32,768 to positive 32,767. Use it when you know the values will be small, to save storage.
SERIAL
A PostgreSQL pseudo-type that creates an auto-incrementing integer column. Behind the scenes, it creates a sequence, adds a NOT NULL constraint, and sets the default value to the next sequence value.
NUMERIC(p, s)
A real number with p total digits and s digits after the decimal point. NUMERIC(6,2) allows values up to 9999.99.
Constraint
A rule applied to a column or table that restricts what data can be inserted or updated. Constraints enforce data integrity.
PRIMARY KEY
A constraint that uniquely identifies each row in a table. It combines NOT NULL (value required) and UNIQUE (no duplicates). Each table should have one.
Composite key
A primary key made up of two or more columns. The combination of those columns must be unique across all rows.
NOT NULL
A constraint that prevents a column from having empty (NULL) values. Use it when data must always be present.
UNIQUE
A constraint that ensures all values in a column (or combination of columns) are distinct. Unlike PRIMARY KEY, it does allow NULL values.
FOREIGN KEY
A constraint that links a column in one table to the primary key of another table. It enforces referential integrity: you cannot insert a value that does not exist in the referenced table, and you cannot delete a referenced row.
Referential integrity
The principle that every foreign key value must correspond to an existing primary key value in the referenced table. It prevents orphaned records.
DEFAULT
A constraint that assigns a preset value to a column when no value is provided during insertion.
CHECK
A constraint that validates data against a Boolean expression before allowing insertion or update. For example, CHECK (price > 0).
SEQUENCE
A PostgreSQL object that generates a sequence of integers. SERIAL uses one behind the scenes. Can be customised with START, INCREMENT, MINVALUE, and MAXVALUE.
ALTER TABLE
The SQL statement used to modify an existing table: add columns, drop columns, change data types, change data sizes, or add/remove constraints.
DROP TABLE
The SQL statement that permanently removes a table and all its data from the database.
CASCADE
An option on DROP TABLE that also removes foreign key constraints referencing the dropped table, allowing it to be dropped even when other tables depend on it.
IF EXISTS
An option on DROP TABLE that prevents an error if the table does not exist. Useful when running scripts with multiple drops.
Basic structure:
CREATE TABLE contact (
contact_id INT PRIMARY KEY,
username VARCHAR(50),
password VARCHAR(50)
);
Naming rules (consistent across most databases):
Must start with a letter
Can contain letters, numbers, and underscores only
Should not contain spaces
Have a character length limit (database-dependent)
One line per column is best practice for readability
Each column needs a name, a data type, and optionally a constraint
A table can have as few as one column (e.g., a newsletter table with just an email column)
Boolean: true, false, or null
CHAR(n): fixed-length string, padded with spaces
VARCHAR(n): variable-length string, up to n characters, no padding
TEXT: unlimited-length string
SMALLINT: 2-byte integer (negative 32,768 to 32,767)
INT: 4-byte integer (negative 2.1 billion to 2.1 billion)
SERIAL: auto-incrementing integer (uses a sequence internally)
FLOAT(n): floating-point number, up to 8 bytes of precision
REAL: 4-byte floating-point number
NUMERIC(p, s): exact number with p digits total, s after the decimal
DATE: date only
TIME: time of day only
TIMESTAMP: date and time combined
PRIMARY KEY = NOT NULL + UNIQUE combined:
contact_id INT PRIMARY KEY
NOT NULL on its own:
username VARCHAR(50) NOT NULL
UNIQUE on its own (allows NULLs, but all non-null values must be distinct):
email VARCHAR(50) UNIQUE
UNIQUE as a table constraint (for composite uniqueness):
UNIQUE(invoice_id, track_id)
FOREIGN KEY:
CONSTRAINT invoice_customer_id_fkey
FOREIGN KEY (customer_id)
REFERENCES customer (customer_id);
Cannot insert a value that does not exist in the referenced table
Cannot delete a row that is referenced by another table's foreign key
DEFAULT:
unit_price NUMERIC DEFAULT 0.99
CHECK:
birth_date DATE CHECK (birth_date > '1900-01-01'),
membership_fee NUMERIC CHECK (membership_fee > 0),
opt_in CHAR(1) CHECK (opt_in IN ('Y', 'N'))
Can reference other columns in the same row: CHECK (joined_date > birth_date)
Constraint names are auto-generated (tablename_columnname_check) but can be customised: CONSTRAINT positive_fee CHECK (membership_fee > 0)
SERIAL creates an auto-incrementing column for primary keys:
CREATE TABLE contact (
contact_id SERIAL PRIMARY KEY,
username VARCHAR(50),
password VARCHAR(50)
);
Behind the scenes, PostgreSQL:
Creates a sequence object
Sets the column to NOT NULL with the default as the next sequence value
Links the sequence's lifecycle to the column (drop the column, drop the sequence)
SERIAL does not automatically make the column a primary key. You must still add PRIMARY KEY explicitly
When inserting, you can omit the SERIAL column and the value auto-generates:
INSERT INTO contact (username, password)
VALUES ('sophia', 'Password1');
Sequences can also be created manually with custom START and INCREMENT values:
CREATE SEQUENCE mysequence START 10 INCREMENT 10;
SELECT nextval('mysequence'); -- returns 10, then 20, then 30...
Validates data on insert and update using a Boolean expression:
CREATE TABLE member (
member_id SERIAL PRIMARY KEY,
birth_date DATE CHECK (birth_date > '1900-01-01'),
joined_date DATE CHECK (joined_date > birth_date),
opt_in CHAR(1) CHECK (opt_in IN ('Y', 'N')),
membership_fee NUMERIC CHECK (membership_fee > 0)
);
If the check fails, the database rejects the change and raises an error
PostgreSQL auto-names constraints as tablename_columnname_check
Can be a column constraint or a table constraint
As a column constraint: username VARCHAR(50) UNIQUE
As a table constraint (composite): UNIQUE(invoice_id, track_id)
Multiple NULL values are permitted in a UNIQUE column
Can be added to an existing table:
ALTER TABLE customer
ADD CONSTRAINT email_unique UNIQUE (email);
Fails if the existing data already contains duplicates in that column
Add a single column:
ALTER TABLE contact ADD username VARCHAR(50);
Add multiple columns:
ALTER TABLE contact
ADD password VARCHAR(50),
ADD email VARCHAR(50);
Drop a column:
ALTER TABLE contact DROP username;
PostgreSQL allows dropping columns that contain data (be careful)
You can mix ADD and DROP in a single statement, but separate commands are best practice
Change a column's data type:
ALTER TABLE contact
ALTER COLUMN opt_in TYPE CHAR(1);
PostgreSQL will cast existing values to the new type. If the cast fails, an error is raised
Converting from INT to CHAR works. Converting from CHAR (with letters) back to INT will fail if any non-numeric characters exist
Cannot change the data type of a column that has a foreign key reference
Change column size:
ALTER TABLE registration
ALTER COLUMN last_name TYPE VARCHAR(50);
Reducing size below what existing data requires will raise an error
Change multiple columns at once:
ALTER TABLE registration
ALTER COLUMN first_name TYPE VARCHAR(50),
ALTER COLUMN email TYPE VARCHAR(100);
Cannot alter a column's type or size if it has a foreign key reference to another table
Basic syntax:
DROP TABLE contact;
Cannot drop a table if another table's foreign key references it
Drop order matters: start with tables that have no foreign keys pointing to them, then work backwards
CASCADE removes the table and any foreign key constraints referencing it:
DROP TABLE customer CASCADE;
IF EXISTS prevents errors when the table does not exist:
DROP TABLE IF EXISTS customer;
In the course database, the safe drop order is:
invoice_line, playlist_track (no foreign keys reference these)
invoice, track, playlist
album, customer
employee, artist, genre, media_type
Every application that stores user accounts uses PRIMARY KEY and UNIQUE constraints on usernames and emails. Foreign keys are how e-commerce databases link orders to customers and order items to products, preventing orphaned records. CHECK constraints enforce business rules at the database level, such as ensuring prices are never negative or birth dates are reasonable. ALTER TABLE is used routinely when business requirements change and new fields need to be added to existing tables without losing data.
Students think SERIAL automatically makes a column a primary key. It does not. You must add PRIMARY KEY separately.
Students assume UNIQUE means NOT NULL. UNIQUE allows multiple NULL values. Only PRIMARY KEY enforces both uniqueness and non-null.
Students try to DROP a parent table before removing or dropping the child table that references it. The foreign key prevents this unless CASCADE is used.
Students forget that changing a data type with ALTER TABLE can fail if existing data cannot be cast to the new type.
⚠️ Know the difference between PRIMARY KEY, UNIQUE, and NOT NULL. PRIMARY KEY = UNIQUE + NOT NULL.
⚠️ Be able to write a complete CREATE TABLE statement with correct data types and at least one constraint.
⚠️ Understand that foreign keys prevent both invalid inserts (referencing a non-existent parent) and invalid deletes (removing a referenced parent).
⚠️ Know the correct DROP TABLE order based on foreign key dependencies.
⚠️ Understand that SERIAL is a pseudo-type that creates a sequence, not a true data type.
True or False: A PRIMARY KEY column can contain NULL values.
Fill in the blank: The ___ pseudo-type in PostgreSQL creates an auto-incrementing integer column.
True or False: You can drop a table that is referenced by a foreign key in another table without using CASCADE.
Fill in the blank: The ___ constraint assigns a preset value to a column when no value is provided during insertion.
True or False: A UNIQUE constraint allows multiple NULL values in the same column.
Answers: 1. False. 2. SERIAL. 3. False. 4. DEFAULT. 5. True.
Q: Write a CREATE TABLE statement for a "product" table with an auto-incrementing primary key, a required product name (up to 100 characters), a price that must be greater than zero, and an optional description.
A: CREATE TABLE product (product_id SERIAL PRIMARY KEY, product_name VARCHAR(100) NOT NULL, price NUMERIC CHECK (price > 0), description TEXT);
Q: What happens if you try to insert a row into the album table with an artist_id that does not exist in the artist table?
A: The database raises a foreign key violation error and rejects the insert. The artist_id must reference an existing row in the artist table.
Q: Write an ALTER TABLE statement that adds an "email" column (up to 100 characters) to the "employee" table.
A: ALTER TABLE employee ADD email VARCHAR(100);
Q: Explain why you cannot simply run DROP TABLE artist; if the album table has a foreign key referencing it.
A: The foreign key constraint on the album table depends on the artist table's primary key. Dropping artist would break referential integrity. You must either drop album first, remove the foreign key constraint, or use DROP TABLE artist CASCADE;.
Q: What is the difference between CHAR(10) and VARCHAR(10)?
A: CHAR(10) always stores exactly 10 characters, padding shorter strings with spaces. VARCHAR(10) stores up to 10 characters without padding. VARCHAR is more storage-efficient for variable-length data.
Table constraints connect directly to data manipulation (Part 4), where INSERT, UPDATE, and DELETE operations must satisfy all constraints or be rejected. Foreign keys are the foundation of multi-table relationships and JOIN queries covered in later units. Understanding data types is essential for aggregate functions (Part 3), where operations like SUM and AVG only work on numeric columns.
CREATE TABLE, ALTER TABLE, DROP TABLE, SQL data types, VARCHAR, CHAR, INT, SERIAL, NUMERIC, BOOLEAN, TEXT, TIMESTAMP, PRIMARY KEY, composite key, FOREIGN KEY, referential integrity, NOT NULL, UNIQUE constraint, CHECK constraint, DEFAULT constraint, auto-increment, SEQUENCE, nextval, CASCADE, IF EXISTS, column constraints, table constraints, database schema, RDB 101, Sophia Pathways, PostgreSQL