SQL CREATE TABLE Statement (Column Types, Constraints, and Table Structure)

Tables, Rows, Columns, and Values

Database Tables

au_id   au_fname   au_lname
-----   --------   --------
A01     Sarah      Buchman
A02     Wendy      Heydemark
A03     Hallie     Hull
A04     Klee       Hull

The CREATE TABLE Statement — Keyword by Keyword

CREATE TABLE BasicTable

This portion tells the SQL engine: "I want to create a new table and it should be named BasicTable." The keywords CREATE TABLE are reserved — the engine recognizes them as the command to define a new base relation. The table name follows immediately. Table names follow the same naming rules as column names: no spaces without quoting, no reserved words without quoting, consistent with your schema's naming convention.

Defining the Column List

After the table name, an opening parenthesis begins the column list. Each column is defined on its own line with a name, a data type, and optional constraints. A comma separates each column definition. A closing parenthesis and a semicolon end the statement:

(
    Firstname   CHAR(50) NULL,
    Lastname    CHAR(50) NULL,
    PhoneNumber CHAR(10) NULL
)

The Complete BasicTable Statement

Combining the declaration and the column list produces the complete CREATE TABLE statement:

CREATE TABLE BasicTable (
    Firstname   CHAR(50) NULL,
    Lastname    CHAR(50) NULL,
    PhoneNumber CHAR(10) NULL
);

Column Data Types

Character Types — CHAR, VARCHAR, TEXT

Character types store text values — names, descriptions, codes, addresses, and any other data that consists of letters, numbers, spaces, and symbols.
CHAR(n) stores fixed-length text. Every stored value is padded to exactly n characters with trailing spaces. CHAR is efficient for columns where every value has the same length — a two-letter state code, a fixed-format product code, a country code.

VARCHAR(n) stores variable-length text up to n characters. Each value occupies only the space it needs plus a small length indicator. VARCHAR is the standard choice for name, address, and description columns where values vary in length. Most databases support VARCHAR lengths up to 65,535 characters or more — not the 255-character limit noted in older documentation.

TEXT (or its equivalents: CLOB, LONGTEXT, NVARCHAR(MAX)) stores text of unknown or very large length — article bodies, notes, log entries, JSON data. TEXT columns typically cannot be indexed directly and have limitations in WHERE clause comparisons.

Numeric Types — INT, BIGINT, DECIMAL, FLOAT

Numeric types store numbers and support arithmetic operations and numeric comparisons in SQL.

INT stores whole numbers — customer IDs, quantities, counts, ages. The standard INT range is approximately −2 billion to +2 billion. BIGINT extends this to approximately −9 quintillion to +9 quintillion, used for large counters, timestamps, and system-generated IDs.

DECIMAL(p,s) stores exact decimal values with p total digits and s digits after the decimal point. DECIMAL(10,2) stores values like 12345678.99 — the correct type for monetary amounts, where floating-point rounding errors are unacceptable.

FLOAT and REAL store approximate floating-point numbers — suitable for scientific measurements where a small margin of imprecision is acceptable, but never appropriate for financial data.

Date and Time Types — DATE, DATETIME, TIMESTAMP

DATE stores a calendar date (year, month, day) with no time component. DATETIME stores both date and time. TIMESTAMP stores a point in time, often with automatic population on INSERT or UPDATE. The specific type names and behaviors vary slightly between database engines — MySQL uses DATETIME and TIMESTAMP distinctly; PostgreSQL uses TIMESTAMP; SQL Server uses DATETIME2.

Boolean Types — BOOLEAN / BIT

BOOLEAN (PostgreSQL, MySQL 8+) or BIT (SQL Server, older MySQL) stores a true/false value. Used for flags, status indicators, and yes/no attributes. In databases that lack a native boolean type, a CHAR(1) column with 'Y'/'N' or a TINYINT with 0/1 is a common substitute.

Why PhoneNumber Is CHAR, Not Numeric

The PhoneNumber column in BasicTable is defined as CHAR(10), not INT or DECIMAL, even though phone numbers contain digits. The reason is that phone numbers are not quantities — you never add two phone numbers together or calculate their average. More importantly, phone numbers include non-numeric characters: parentheses around area codes, hyphens between segments, plus signs for international prefixes, and leading zeros that numeric types silently drop. A phone number stored as INT loses the leading zero from numbers like 0207-123-4567. Stored as CHAR(10), the value is preserved exactly as entered.

This principle extends to any identifier that looks numeric but is not a quantity: postal codes, social security numbers, product codes, ISBN numbers. When the digits represent an identifier rather than a measurable value, use a character type.

Data Type Reference

Column Constraints

CREATE TABLE BasicTable (
    Firstname   CHAR(50)     NULL,
    Lastname    CHAR(50) NOT NULL,
    PhoneNumber CHAR(10)     NULL
);

PRIMARY KEY

A primary key uniquely identifies each row in the table. No two rows can share the same primary key value, and the primary key column cannot be NULL. In practice, most tables use a system-generated integer as their primary key — a value the database engine assigns automatically, guaranteed to be unique:

CREATE TABLE Contacts (
ContactID   INT      NOT NULL AUTO_INCREMENT,
Firstname   CHAR(50)     NULL,
Lastname    CHAR(50) NOT NULL,
PhoneNumber CHAR(10)     NULL,
PRIMARY KEY (ContactID)
);

Named Constraints with the CONSTRAINT Keyword

Constraints can be given explicit names using the CONSTRAINT keyword. Named constraints are easier to reference in error messages, easier to drop with ALTER TABLE, and make the schema's intent clearer:

CREATE TABLE Contacts (
ContactID   INT      NOT NULL,
Firstname   CHAR(50)     NULL,
Lastname    CHAR(50) NOT NULL,
PhoneNumber CHAR(10)     NULL,
CONSTRAINT pk_contacts PRIMARY KEY (ContactID)
);

IF NOT EXISTS — The Safe CREATE TABLE Pattern

Running a CREATE TABLE statement against a database that already contains a table with the same name produces an error. The IF NOT EXISTS clause prevents this — the statement executes only if no table with that name exists, and silently succeeds (does nothing) if the table already exists:

CREATE TABLE IF NOT EXISTS Contacts (
    ContactID   INT      NOT NULL,
    Firstname   CHAR(50)     NULL,
    Lastname    CHAR(50) NOT NULL,
    PhoneNumber CHAR(10)     NULL,
    CONSTRAINT pk_contacts PRIMARY KEY (ContactID)
);

Database Engines and Column Type Variations

Create Table - Exercise