Lesson 12 — Defining Structure

CREATE TABLE

CREATE TABLE defines the schema. Getting the data types, constraints, and indexes right here prevents problems that are expensive to fix in production.

SQL — Full CREATE TABLE 
-- Full CREATE TABLE with all constraint types:
CREATE TABLE assessments (
    assessment_id   INT           NOT NULL AUTO_INCREMENT,
    learner_id      INT           NOT NULL,
    title           VARCHAR(200)  NOT NULL,
    category        VARCHAR(50)   NOT NULL DEFAULT 'Written',
    due_date        DATE          NOT NULL,
    submitted_at    DATETIME,                    -- nullable — may not be submitted yet
    score           DECIMAL(5, 2),               -- nullable — not yet marked
    feedback_notes  TEXT,
    is_final        BOOLEAN       NOT NULL DEFAULT FALSE,
    created_at      TIMESTAMP     DEFAULT CURRENT_TIMESTAMP,

    -- Named constraints:
    CONSTRAINT pk_assessments    PRIMARY KEY (assessment_id),
    CONSTRAINT fk_assess_learner FOREIGN KEY (learner_id)
                                 REFERENCES learners(learner_id)
                                 ON DELETE CASCADE,
    CONSTRAINT chk_score         CHECK (score BETWEEN 0 AND 100),
    CONSTRAINT chk_category      CHECK (category IN ('Written','Practical','Oral','Portfolio')),
    CONSTRAINT uq_learner_title  UNIQUE (learner_id, title)  -- one submission per title per learner
);
SQL — CREATE TABLE variants 
-- CREATE TABLE IF NOT EXISTS — safe for scripts that may run multiple times:
CREATE TABLE IF NOT EXISTS sessions (
    session_id    INT  AUTO_INCREMENT PRIMARY KEY,
    learner_id    INT  NOT NULL REFERENCES learners(learner_id),
    session_date  DATE NOT NULL,
    attendance    ENUM('Present','Absent','Late','Excused') DEFAULT 'Present',
    notes         TEXT
);

-- CREATE TABLE LIKE — copy structure without data (MySQL):
CREATE TABLE learners_backup LIKE learners;

-- CREATE TABLE AS SELECT — copy structure AND data:
CREATE TABLE learners_archive AS
SELECT * FROM learners WHERE is_active = FALSE;

Practice Task

Your Turn

Write a complete CREATE TABLE for a placements table (job placements for graduates): placement_id (PK), learner_id (FK), company_name, job_title, start_date, end_date (nullable), monthly_salary (DECIMAL), city, province, is_current (BOOLEAN, default TRUE). Add CHECK constraints on salary (> 0) and appropriate NOT NULL constraints.

Common Mistakes

  • No primary key — every table needs one for referential integrity and update/delete reliability.
  • Using TEXT for columns that need indexes — TEXT columns cannot be fully indexed in MySQL.
  • Missing NOT NULL on required columns — nullable columns require NULL handling in every query.
  • CHECK constraints not enforced in older MySQL (pre-8.0) — test your constraints.

Professional Tip

Write your CREATE TABLE statements in a migration file and run them on an empty schema first to verify they work. Use IF NOT EXISTS so the script is idempotent — safe to run multiple times.

Mini Quiz

What does IF NOT EXISTS do in CREATE TABLE?