Lesson 15 of 20

In progress

Course progress

SQL

CREATE TABLE with sensible types

Design a table column by column: types that fit the data, defaults that make inserts short, and names that survive a schema change.

Creating a table is a design decision with consequences that outlive the query. Types, defaults and names are cheap to get right at the start and expensive to change once data exists.

What you will learn

  • Choose types that match the data and how it is queried.
  • Use defaults and generated keys.
  • Rename and reshape a table without losing data.

A complete example

SQL
Editable. Runs in your browser.





Press Run to see the output.

Every column has a type, and the important ones have constraints. CHECK documents the allowed values in the schema, where the next developer will see them, rather than in a comment.

Types worth choosing

  • INTEGER for ids, counts, and money in the smallest unit.
  • TEXT for names, statuses, and ISO dates.
  • REAL for measurements where approximation is acceptable.
  • BLOB only for binary data, rarely in application tables.

Money as an integer in paise avoids the rounding drift of floating point. A price stored as REAL will eventually produce a total that is off by a paisa, and someone will spend a day on it.

Dates as TEXT in YYYY-MM-DD sort correctly, compare correctly, and survive JSON. A timestamp is best as ISO 8601 with a timezone, or as an integer of seconds since the epoch if you need arithmetic.

Statuses are tempting to store as free text. A CHECK constraint or a reference table keeps typos out; 'active', 'Active' and 'ACTIVE' are three different statuses to a database.

Defaults and generated keys

SQL
Editable. Runs in your browser.





Press Run to see the output.

An INTEGER PRIMARY KEY in SQLite is an alias for the rowid, so it is generated automatically and is efficient to look up. Other databases use SERIAL, IDENTITY or a sequence; the concept is the same.

Defaults shorten inserts and make the common case correct. Keep them simple: a default that calls a function is evaluated per insert, and a default that references another column is not allowed.

Naming

Use snake_case, singular table names or plural consistently, and name the primary key id in every table. Foreign keys are named <table>_id, which makes joins read naturally: orders.customer_id = customers.id.

Avoid reserved words as identifiers. order, group and user need quoting in some databases and confuse readers in all of them; orders and members do not.

Column names should describe the value, not its widget. started_on is better than start_date_input, and price_paise carries its unit so nobody has to guess.

Changing a table

SQL
Editable. Runs in your browser.





Press Run to see the output.

ADD COLUMN is widely supported. Dropping or renaming a column is supported in modern SQLite, PostgreSQL and MySQL, with different syntax; older versions require the rebuild pattern:

  1. Create a new table with the desired shape.
  2. Copy the rows across, converting as needed.
  3. Drop the old table.
  4. Rename the new one.

That pattern is worth knowing because it is also how you change a type or add a constraint to existing data.

Migrations, not manual edits

A schema change made by hand in a database client is invisible to everyone else and impossible to reproduce on staging. Keep schema statements in numbered files:

migrations/001_create_subscriptions.sql
migrations/002_add_cancelled_on.sql

Each file runs once, in order, and the sequence is the schema’s history. When a change goes wrong, the history explains what the database currently contains — which is more valuable than the schema alone.

Creating a table without a primary key because "it is only a log". Without one, rows cannot be referenced, updated precisely or deduplicated, and the table becomes impossible to clean up later.

Design a books table with an id, a title, an author reference, a price in paise, a publication year and a status limited to draft, published and retired. Insert two rows using defaults where possible.

Show solution
SQL
Editable. Runs in your browser.





Press Run to see the output.

Two rows, both with a generated id and today’s date; the first uses the default status of draft. The CHECK constraints reject a negative price, an impossible year, or an unknown status.

Recap

  • Types carry meaning: integers for money, text for ISO dates, constraints for allowed values.
  • Defaults and generated keys keep inserts short and correct.
  • Change schemas through numbered migration files, and rebuild a table when the engine cannot alter it in place.

Module 4 covers the guarantees that keep a schema honest under real use: constraints, indexes, transactions and views.

Should every table have an id?
Almost always. A natural key such as an email can change; a surrogate id cannot.
Is TEXT bad for dates?
Not in SQLite, where it is the norm. ISO text sorts and compares correctly. Use a date type when the database has one.

Progress is saved in this browser only. No account, no email.

Admin

About the author

Admin

Instructor and technical writer at tutorials.in

Writes beginner lessons for tutorials.in. Each lesson is checked against the language or tool documentation, and examples are run before publishing.

Reviewed by the editorial team before publishing. Last updated November 16, 2026.