KWAS Academy
Learn. Build. Master.
QUICK START:HTMLCSSJavaScriptTypeScriptPythonSQLReactNext.jsNode.jsDSASystem DesignDevOpsCybersecurityAI / ML
Beginner 16 min readModule: Module 2: Data Definition (CREATE, ALTER, DROP) & Data Types

DDL: CREATE TABLE, Constraints & Data Types

Define relational tables with PRIMARY KEY, FOREIGN KEY, NOT NULL, UNIQUE, and CHECK constraints.

What You Will Learn in This Lesson

  • Choosing optimal data types (VARCHAR, INT, UUID, NUMERIC, TIMESTAMPTZ)
  • Enforcing entity integrity with PRIMARY KEY and UNIQUE constraints
  • Referential integrity with FOREIGN KEY ... REFERENCES ... ON DELETE CASCADE

Introduction & Core Concept

Data Definition Language (DDL) commands define and modify the schema structure of your relational database tables and indexes.
WHY DOES THIS MATTER IN THE REAL WORLD?

Declaring strict constraints at the database level guarantees data integrity even if application code contains bugs.

CREATE TABLE with Relational Constraints

sql
sql
1
2
3
4
5
6
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email VARCHAR(255) NOT NULL UNIQUE,
    age INT CHECK (age >= 13),
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

Line-by-Line Technical Breakdown

1ALTER TABLE allows adding or dropping columns on existing production tables.

Try It Yourself (Interactive Editor)

Modify the code in real-time and click Run to test live browser output and console logs.

Intelligent Code Runner & Live Sandbox[SQL]
SQL SOURCE EDITOR
Interactive Live Code

Industry Best Practices & Professional Standards

  • Always store dates with timezone (TIMESTAMPTZ).

Lesson Summary & Core Takeaways

  • DDL establishes the structural integrity rules of your database.