Creating Tables and Data Types

CREATE TABLE defines a new table's structure: its columns, each column's data type, and any rules on what values are allowed.

CREATE TABLE users (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(255) NOT NULL,
  signup_date DATE DEFAULT CURRENT_DATE,
  is_active BOOLEAN DEFAULT TRUE
);

Each line names a column, followed by its data type, followed by any constraints on it.

Common data types

| Type | Stores | Example | |---|---|---| | INT | Whole numbers | 42 | | DECIMAL(p, s) | Exact fixed-point numbers (money, measurements) | DECIMAL(10,2) → 19.99 | | VARCHAR(n) | Variable-length text, up to n characters | VARCHAR(100) | | TEXT | Long-form text with no practical length cap | a blog post body | | DATE / TIMESTAMP | A calendar date, or a date and time | 2026-09-15 | | BOOLEAN | True/false | TRUE |

DECIMAL deserves a specific callout: never use a floating-point type (FLOAT, DOUBLE) for money. Floating-point numbers can't represent most decimal fractions exactly in binary, so repeated arithmetic on prices accumulates small rounding errors. DECIMAL(10,2) stores the value as an exact decimal instead — 10 total digits, 2 of them after the decimal point.

Column constraints

Constraints restrict what values a column will accept, enforced by the database itself rather than by application code (and get a fuller lesson later in this course):

• NOT NULL — the column can't be left empty.
• DEFAULT value — if no value is given on insert, use this one.
• PRIMARY KEY — uniquely identifies each row (its own dedicated lesson is coming up next).
• UNIQUE — no two rows can share the same value in this column.

AUTO_INCREMENT / SERIAL

Most tables want an ID that's assigned automatically and never repeats. MySQL uses AUTO_INCREMENT, PostgreSQL traditionally uses SERIAL (or the newer, standard GENERATED ALWAYS AS IDENTITY) — another example of the dialect differences mentioned in the first lesson, all accomplishing the same goal: hand the database responsibility for generating unique, sequential IDs so your application never has to.

Inserting a row

Once a table exists, INSERT adds data to it:

INSERT INTO users (name, email) VALUES ('Ada Lovelace', 'ada@example.com');

Columns not listed here (id, signup_date, is_active) fall back to their defaults — AUTO_INCREMENT generates the id, and signup_date/is_active use the DEFAULT values from the table definition.

With a table to query against, the next lessons dig into WHERE, ORDER BY, and the rest of SELECT's filtering and sorting tools.