Creating Tables and Constraints

Every table used throughout this course started with a CREATE TABLE statement. This lesson covers it properly, column definitions, and the constraints beyond primary and foreign keys that stop bad data from ever being stored in the first place.

Learning Objectives

• Write a CREATE TABLE statement with columns, types, and constraints.
• Apply NOT NULL, UNIQUE, CHECK, and DEFAULT to enforce rules at the database level.
• Modify an existing table's structure with ALTER TABLE.

A Full CREATE TABLE Example

Every piece here, column types, NOT NULL, UNIQUE, CHECK, DEFAULT, and the foreign key, is standard SQL and works with this same syntax across MySQL, PostgreSQL, SQL Server, and SQLite.

CREATE TABLE employees (
  id INTEGER PRIMARY KEY,
  first_name VARCHAR(100) NOT NULL,
  email VARCHAR(255) NOT NULL UNIQUE,
  salary DECIMAL(10,2) CHECK (salary >= 0),
  status VARCHAR(20) DEFAULT 'active',
  department_id INTEGER,
  FOREIGN KEY (department_id) REFERENCES departments(id)
);

The Core Constraints

CHECK Constraints

CHECK lets you enforce rules that go beyond type and uniqueness, a salary can never be negative, a status must be one of a known set of values, directly in the schema rather than only in application code.

salary DECIMAL(10,2) CHECK (salary >= 0),
status VARCHAR(20) CHECK (status IN ('active', 'inactive', 'on_leave'))

Altering an Existing Table

ALTER TABLE changes a table's structure after it already exists, often after it already has data. Adding a column is well supported everywhere, but more complex changes, dropping a column, changing a column's type, used to be far more limited in SQLite specifically than in the other three databases, worth checking your SQLite version if a more advanced ALTER operation fails.

ALTER TABLE employees
ADD COLUMN hire_date DATE;

ALTER TABLE employees
ADD COLUMN hire_date DATE;
-- SQLite supports adding a column this way, but has historically had
-- limited support for other ALTER operations like dropping or renaming
-- a column, improved significantly in SQLite 3.25+ (2018) and later.

Dropping a Table

DROP TABLE removes the table entirely, structure and all data, with no undo outside of a backup. IF EXISTS prevents an error if the table doesn't exist, useful in setup scripts that might run more than once.

DROP TABLE IF EXISTS employees;

Dropping an Entire Database

DROP DATABASE IF EXISTS company_db;

All Five, Side by Side

It's worth being explicit about that last row: there's no such thing as DELETE DATABASE or TRUNCATE DATABASE in SQL. DELETE and TRUNCATE are both scoped to one table at a time, no matter how many rows or how much of it they remove. DROP DATABASE is the only statement in this course that operates on an entire database at once, which is exactly why it's the most dangerous one here.

Frequently Asked Questions

Common Beginner Mistakes

Best Practices

• Enforce rules at the database level with constraints, don't rely solely on application code to catch bad data.
• Use CHECK for any rule that's genuinely about the data's validity, not just business logic that might change often.
• Use DEFAULT for columns that have an obvious, sensible value most of the time, like a status defaulting to 'active'.
• Always use DROP TABLE IF EXISTS and CREATE TABLE IF NOT EXISTS in setup/migration scripts meant to be re-runnable.

Interview Questions

Summary

This lesson covered a full CREATE TABLE statement with NOT NULL, UNIQUE, CHECK, and DEFAULT constraints, a historical MySQL gotcha with CHECK, modifying an existing table with ALTER TABLE, and safely dropping a table with DROP TABLE IF EXISTS.

What's Next?

The next lesson covers indexes, how they make queries fast, and the tradeoffs that come with adding them.