Creating and Altering Tables

Our University database is already useful, but real systems evolve. New requirements appear—perhaps we need to track advisors, course sections, or lab schedules. This lesson shows how to create new tables, define constraints, and adapt existing structures safely.

Learning Goals

• Create tables that extend the University schema
• Choose data types and constraints that match academic 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 the registrar wants to store advising assignments.

Create an advisor table

CREATE TABLE advisor (
  advisor_id    INTEGER PRIMARY KEY,
  first_name    TEXT      NOT NULL,
  last_name     TEXT      NOT NULL,
  email         TEXT      NOT NULL UNIQUE,
  dept_id       INTEGER   NOT NULL,
  FOREIGN KEY (dept_id) REFERENCES department(dept_id)
);

Now link advisors to students, including optional notes:

Create a student_advisor bridge table

CREATE TABLE student_advisor (
  student_id INTEGER NOT NULL,
  advisor_id INTEGER NOT NULL,
  assigned_on DATE    NOT NULL DEFAULT CURRENT_DATE,
  notes       TEXT,
  PRIMARY KEY (student_id, advisor_id),
  FOREIGN KEY (student_id) REFERENCES student(student_id),
  FOREIGN KEY (advisor_id) REFERENCES advisor(advisor_id)
);

This design enforces that every student-advisor relationship references valid records on both sides.

Choosing Data Types and Constraints

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

Course sections with constraints

CREATE TABLE course_section (
  section_id     INTEGER PRIMARY KEY,
  course_id      INTEGER NOT NULL,
  term           TEXT    NOT NULL,
  meeting_days   TEXT    NOT NULL,             -- e.g., 'MWF'
  meeting_time   TEXT    NOT NULL,             -- e.g., '09:30'
  capacity       INTEGER NOT NULL CHECK (capacity > 0),
  FOREIGN KEY (course_id) REFERENCES course(course_id)
);

• CHECK (capacity > 0) guarantees positive capacities.
• We store meeting patterns as text for simplicity, but you could normalize them further.

Altering Existing Tables

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

Add a Column

Track student phone numbers

ALTER TABLE student
ADD COLUMN phone TEXT;

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

Modify a Column

SQLite limits some alteration operations, but in engines that support it you can widen text fields or change defaults:

ALTER TABLE course
ALTER COLUMN course_title TYPE TEXT;         -- PostgreSQL
ALTER TABLE course
ALTER COLUMN credits SET DEFAULT 4;

When your engine lacks direct support (like SQLite), create a new table, copy data, and rename—most ORMs provide helpers for this.

Drop a Column

Remove optional phone numbers

ALTER TABLE student
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 course RENAME COLUMN course_title TO title;
ALTER TABLE student RENAME TO student_profile;

Add a constraint after table creation

ALTER TABLE course
ADD CONSTRAINT course_code_unique UNIQUE (course_code);

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 University 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 advisor email is different?
   A) PRIMARY KEY
   B) NOT NULL
   C) UNIQUE
   D) CHECK

2. Why does the student_advisor table use a composite primary key?
   A) To auto-increment advisor IDs
   B) To allow duplicate assignments
   C) To prevent the same student-advisor 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