PART 3 • SQL MASTERY

Define Structures That Reject Invalid Data

Turn a relational design into clear SQL objects, keys and constraints—and evolve the schema without treating production data carelessly.

Level 10 of 18Core SQL130–170 minutesLevel 9 recommended

BY THE END, YOU CAN

Create tables with suitable types.
Choose column- and table-level constraints.
Define composite and foreign keys.
Explain referential actions.
Plan safe ALTER operations.
Distinguish DELETE, TRUNCATE and DROP.

01 • DEFINE THE DATABASE CONTRACT

DDL Specifies Structure and Enforceable Rules

A schema is a contract shared by applications, reports and future developers.

CREATE

Introduce an object

Create schemas, tables, views, indexes and other database objects.

→

ALTER

Evolve an object

Add, change, rename or remove columns and constraints.

→

TRUNCATE

Remove table rows

Quickly empties a table while retaining its definition.

→

DROP

Remove the object

Deletes the definition and normally its stored data.

02 • CREATE A RELATION

CREATE TABLE Combines Names, Domains and Constraints

CREATE TABLE students (
    student_id  INTEGER,
    name        VARCHAR(100) NOT NULL,
    email       VARCHAR(255),
    cgpa        DECIMAL(3,2) DEFAULT 0.00,
    dept_id     INTEGER,

    CONSTRAINT pk_students
        PRIMARY KEY (student_id),
    CONSTRAINT uq_students_email
        UNIQUE (email),
    CONSTRAINT ck_students_cgpa
        CHECK (cgpa BETWEEN 0 AND 10),
    CONSTRAINT fk_students_department
        FOREIGN KEY (dept_id)
        REFERENCES departments(dept_id)
);

Table identity

students names the relation.

Column definitions

Each column has a name, domain and optional rules.

Named constraints

Names make future error diagnosis and ALTER operations clearer.

Schema qualification

academic.students can identify an owning namespace.

GOOD NAMINGstudent_idcourse_titleenrolments

Use consistent, descriptive identifiers; avoid spaces, punctuation, vague abbreviations and reserved words.

03 • ENFORCE VALID STATES

Constraints Place Rules at the Data Boundary

NOT NULL

Value is required

name VARCHAR(100) NOT NULL

Prevents missing values in the column.

UNIQUE

No repeated key value

UNIQUE (email)

NULL treatment can differ by DBMS.

PRIMARY KEY

Chosen row identity

PRIMARY KEY (student_id)

Combines uniqueness and non-null identity.

FOREIGN KEY

Valid reference

REFERENCES departments(dept_id)

Protects referential integrity.

CHECK

Row-level condition

CHECK (cgpa BETWEEN 0 AND 10)

Rejects rows when the condition is FALSE; understand target NULL behavior.

DEFAULT

Value when omitted

DEFAULT CURRENT_DATE

Does not replace an explicitly supplied NULL unless other logic exists.

COLUMN LEVELemail VARCHAR(255) UNIQUE

Convenient for a rule involving one column.

TABLE LEVELPRIMARY KEY (student_id, course_id)

Required for composite constraints and often clearer when naming rules.

04 • DEFINE RELATIONSHIPS

A Foreign Key Requires a Matching Parent Key

PARENTdepartmentsdept_id, dept_name

1 ─── ∞

CHILDstudentsstudent_id, name, dept_id (FK)

RESTRICT / NO ACTION

Reject a parent delete while child references exist. Timing details can differ.

CASCADE

Propagate parent DELETE or UPDATE to child rows. Powerful and potentially broad.

SET NULL

Replace the child FK with NULL; the child column must allow NULL.

SET DEFAULT

Use the child default where supported; that value must satisfy the reference.

Choose referential actions from business meaning

Do not add CASCADE merely for convenience. Deleting a department should rarely delete every historical student record automatically.

05 • EVOLVE A LIVE SCHEMA

ALTER TABLE Must Respect Existing Rows

ADDALTER TABLE students ADD COLUMN phone VARCHAR(20);

A nullable column is usually compatible with existing rows.

BACKFILL THEN REQUIREADD status ...; UPDATE ...; ALTER ... SET NOT NULL;

Populate valid values before applying a strict constraint.

RENAMEALTER TABLE students RENAME COLUMN name TO full_name;

Update queries, views, application code and reports together.

CHANGE TYPEALTER COLUMN cgpa TYPE DECIMAL(4,2);

Confirm conversion, range, locking and dialect syntax.

DROP COLUMNALTER TABLE students DROP COLUMN legacy_code;

Check dependencies and backups before irreversible removal.

SAFER PRODUCTION PATTERN

1. Expand

Add the new structure without breaking old code.

2. Migrate

Backfill, validate and switch reads/writes.

3. Contract

Remove old structure only after dependants are updated.

06 • KNOW THE BLAST RADIUS

DELETE, TRUNCATE and DROP Are Not Interchangeable

Statement	Removes	WHERE?	Structure remains?	Main use
DELETE	Selected or all rows	Yes	Yes	Controlled row removal
TRUNCATE	All rows	No	Yes	Fast complete table clearing
DROP TABLE	Object and its data	No	No	Remove an obsolete table

Before destructive DDLConfirm environmentInspect dependenciesTake or verify backupReview permissionsPlan recoverySchedule impact

07 • DESIGN AND GENERATE

Interactive CREATE TABLE Builder

Edit columns, choose constraints and generate explained SQL. This learning tool does not execute the statement.

Choose a preset or define columns to generate SQL.

08 • EVALUATE CHANGE RISK

Schema Migration Safety Laboratory

Select a proposed change to see its compatibility risk and a safer rollout sequence.

09 • CHECK YOUR UNDERSTANDING

Ten Formative Concept Checks

1. Which statement defines a new table?

2. A primary key guarantees:

3. Which is needed for a composite primary key?

4. A foreign key primarily enforces:

5. DEFAULT is used when:

6. Safest common way to add a required column to populated data?

7. Which statement removes all rows but retains the table definition?

8. DROP TABLE removes:

9. ON DELETE CASCADE should be chosen based on:

10. DDL rollback behavior is:

Answered correctly: 0 of 10

10 • EXPLAIN & PREPARE

University and Interview Questions

2-MARK QUESTIONS

Define DDL.
PRIMARY KEY versus UNIQUE?
What is a CHECK constraint?
Define referential integrity.
TRUNCATE versus DROP?

5-MARK / PRACTICAL

Create STUDENT with all major constraints.
Create ENROLMENT with a composite key.
Explain referential actions.
Plan a safe required-column migration.

INTERVIEW QUESTIONS

Why name constraints?
Can UNIQUE contain NULL?
When is CASCADE dangerous?
How do you change a type safely?
What is expand-and-contract?

Show the table-design answer format

State the relation purpose and grain.
Choose stable column names and domains.
Declare a minimal primary key.
Add required, uniqueness and range constraints.
Define foreign keys and justified actions.
Name important constraints.
Provide representative valid and invalid rows.
Mention dialect assumptions and migration impact.

LEVEL 10 SUMMARY

You Can Now Turn a Design into Enforced SQL

DDL defines and evolves database objects.
Types and constraints form the schema contract.
Composite constraints belong at table level.
Foreign-key actions follow business lifecycle.
ALTER must account for existing rows and dependent code.
DELETE, TRUNCATE and DROP have different scopes.
Production changes need validation, backup and recovery planning.

COURSE CHECKPOINT

Mark this level when you can create a constrained schema and explain how to evolve or remove it safely.

Saved in this browser only.