Lesson 03 ~25 min read

Creating Tables and Defining Structure

Tables are where all your data lives. Getting their structure right — choosing correct data types, defining primary keys, and adding the right constraints — prevents bad data from ever entering your database.

CREATE TABLE

Once you have a database selected, you define the tables that will hold your data. CREATE TABLE specifies the table name, every column, and the rules that apply to each column — its data type, whether it can be empty, and whether it uniquely identifies a row.

sql — basic syntax

CREATE TABLE table_name (
  column_name  data_type  constraints,
  column_name  data_type  constraints,
  ...
);

sql — real example

CREATE TABLE students (
  student_id   INT           NOT NULL AUTO_INCREMENT,
  full_name    VARCHAR(100)  NOT NULL,
  email        VARCHAR(150)  NOT NULL UNIQUE,
  date_of_birth DATE         NOT NULL,
  enrolled_on  DATE          DEFAULT (CURRENT_DATE),
  is_active    BOOLEAN       DEFAULT TRUE,
  PRIMARY KEY (student_id)
);

-- Safe version — only creates if table doesn't exist
CREATE TABLE IF NOT EXISTS students ( ... );

Core Data Types

Every column must have a data type. The type tells the database what kind of values to expect and how much storage to allocate. Choosing the right type prevents invalid data and saves storage space.

INT

Whole numbers. Range: -2,147,483,648 to 2,147,483,647. Use for IDs, counts, ages. Variants: TINYINT (0–255), BIGINT (very large numbers).

VARCHAR(n)

Variable-length text up to n characters. Only uses storage it needs. Use for names, emails, addresses. VARCHAR(100) stores up to 100 characters.

TEXT

Long text with no defined limit. Use for descriptions, articles, notes. Not ideal for columns you search or sort — use VARCHAR for those.

DATE

Stores a date as YYYY-MM-DD. Example: 2025-03-15. No time component. Use for birthdays, enrollment dates, deadlines.

DATETIME

Stores date and time: YYYY-MM-DD HH:MM:SS. Use for timestamps, created_at, updated_at columns.

DECIMAL(p, s)

Exact decimal numbers. DECIMAL(10, 2) stores up to 10 digits, 2 after the decimal point. Always use for money — never FLOAT.

BOOLEAN

TRUE or FALSE. Stored as 1/0 in MySQL. Use for flags: is_active, is_verified, has_paid.

Primary Keys

A primary key is the column (or combination of columns) that uniquely identifies every row in the table. Rules: it must be unique, it cannot be NULL, and each table should have exactly one.

AUTO_INCREMENT tells MySQL to automatically assign the next available integer when a new row is inserted — you never have to specify the ID manually.

sql — primary key options

-- Method 1: inline on the column (most common)
CREATE TABLE courses (
  course_id  INT  PRIMARY KEY AUTO_INCREMENT,
  title      VARCHAR(150) NOT NULL
);

-- Method 2: as a table constraint (same result)
CREATE TABLE courses (
  course_id  INT  NOT NULL AUTO_INCREMENT,
  title      VARCHAR(150) NOT NULL,
  PRIMARY KEY (course_id)
);

-- Composite primary key (two columns together)
CREATE TABLE enrolments (
  student_id  INT NOT NULL,
  course_id   INT NOT NULL,
  enrolled_on DATE,
  PRIMARY KEY (student_id, course_id)
);

DESCRIBE — Inspecting a Table

DESCRIBE (or DESC) shows you the structure of an existing table — its columns, data types, whether NULL is allowed, keys, and default values. Essential for understanding a database you didn't build yourself.

sql

DESCRIBE students;
-- or shorthand:
DESC students;

-- Output:
-- +--------------+--------------+------+-----+---------+----------------+
-- | Field        | Type         | Null | Key | Default | Extra          |
-- +--------------+--------------+------+-----+---------+----------------+
-- | student_id   | int          | NO   | PRI | NULL    | auto_increment |
-- | full_name    | varchar(100) | NO   |     | NULL    |                |
-- | email        | varchar(150) | NO   | UNI | NULL    |                |
-- | date_of_birth| date         | NO   |     | NULL    |                |
-- | enrolled_on  | date         | YES  |     | today   |                |
-- | is_active    | tinyint(1)   | YES  |     | 1       |                |
-- +--------------+--------------+------+-----+---------+----------------+

-- Also useful:
SHOW TABLES;           -- list all tables in current database
SHOW CREATE TABLE students;  -- shows full CREATE TABLE statement

// Knowledge Check

You need to store a product price that could be 9.99 or 1,250.00. Which data type is most appropriate?