Learn SQL  /  Data Modification  /  CREATE TABLE & Constraints

CREATE TABLE & Constraints

CREATE TABLE defines a new table's structure — its columns, their types, and the rules (constraints) that keep the data valid.

Basic CREATE TABLE

R Code
CREATE TABLE students (
  id     INTEGER,
  name   TEXT,
  age    INTEGER,
  score  REAL,
  class  TEXT
);

Common Data Types

  • INTEGER / INT — whole numbers
  • REAL / FLOAT / DECIMAL(p,s) — decimal numbers
  • TEXT / VARCHAR(n) — text, with an optional maximum length
  • DATE / DATETIME — calendar dates and timestamps
  • BOOLEAN — true/false (some databases use INTEGER 0/1 instead)

Exact type names vary a little by database (PostgreSQL, MySQL, and SQLite each have their own quirks) - always check your specific database\'s documentation for the precise list.

PRIMARY KEY and NOT NULL

R Code
CREATE TABLE students (
  id     INTEGER PRIMARY KEY,
  name   TEXT NOT NULL,
  age    INTEGER,
  score  REAL DEFAULT 0
);

PRIMARY KEY uniquely identifies each row and cannot repeat or be NULL. NOT NULL forces a value to always be provided. DEFAULT supplies a value automatically when none is given.

FOREIGN KEY — Linking Tables

R Code
CREATE TABLE employees (
  emp_id    INTEGER PRIMARY KEY,
  name      TEXT NOT NULL,
  dept_id   INTEGER,
  FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
);
💡 Tip
A FOREIGN KEY constraint stops you from inserting a dept_id that does not actually exist in the departments table - it is the database enforcing referential integrity for you, rather than trusting application code to always get it right.

Advanced: UNIQUE and CHECK Constraints

🚀 Advanced Example
UNIQUE prevents duplicate values in a column; CHECK enforces a custom validation rule directly at the database level.
R Code
CREATE TABLE students (
  id     INTEGER PRIMARY KEY,
  email  TEXT UNIQUE,
  score  REAL CHECK (score >= 0 AND score <= 100)
);

Key Points

  • CREATE TABLE defines column names, types, and constraints that keep future data valid.
  • PRIMARY KEY uniquely identifies each row; FOREIGN KEY links a column to another table's primary key.
  • NOT NULL, DEFAULT, UNIQUE, and CHECK are common column-level constraints.
  • Enforcing rules at the database level (constraints) is more reliable than only checking them in application code.