Skip to content
Tutorials Center
My learning

Topics

Lesson 5 of 40 ยท Getting Started

Working with Tables in MariaDB

Create, inspect, change, copy, empty and drop MariaDB tables with CREATE TABLE, SHOW TABLES, ALTER TABLE, TRUNCATE and DROP, with runnable examples and the traps to avoid.

Tables hold your data, so most of your design work happens in CREATE TABLE and ALTER TABLE. This lesson covers the whole life of a table: creating it, listing and inspecting it, changing it, emptying it and dropping it. Standard parts run live below on SQLite; MariaDB specifics are shown read-only.

CREATE TABLE

SQL
CREATE TABLE products (
  id          INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name        VARCHAR(100) NOT NULL,
  price       DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  stock       INT NOT NULL DEFAULT 0,
  created_at  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_products_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

The parts:

  • Each column has a name, a data type (see data types), and options: NOT NULL, DEFAULT, AUTO_INCREMENT.
  • PRIMARY KEY uniquely identifies a row, and UNIQUE KEY forbids duplicates in other columns.
  • ENGINE=InnoDB picks the storage engine (the default in current versions). CHARSET and COLLATE set the text rules for this table.
  • IF NOT EXISTS after CREATE TABLE avoids an error if the table exists.
  • CREATE TEMPORARY TABLE makes a table visible only to your session, deleted when you disconnect.

You can create a table from a query, or copy only the structure of another:

SQL
CREATE TABLE products_backup AS SELECT * FROM products;     -- structure (partly) and data
CREATE TABLE products_empty LIKE products;                    -- exact structure, keys, no data

CREATE TABLE ... AS SELECT does not copy indexes or AUTO_INCREMENT, whereas LIKE does. Use LIKE followed by INSERT ... SELECT for a faithful copy.

Try a standard version in the runner. SQLite lets you write INTEGER PRIMARY KEY AUTOINCREMENT where MariaDB writes INT AUTO_INCREMENT PRIMARY KEY:

SQL Create a table and add a row

Press Run (or Ctrl + Enter) to see the result here.

SHOW TABLES and DESCRIBE

SQL
SHOW TABLES;
SHOW TABLES LIKE 'prod%';
SHOW FULL TABLES;                -- adds Table_type: BASE TABLE or VIEW
DESCRIBE products;               -- columns
SHOW CREATE TABLE products\G     -- full definition

A standard way that also works in MariaDB, MySQL and PostgreSQL is information_schema.tables. In the browser runner the equivalent catalog is sqlite_master:

SQL List the tables ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

ALTER TABLE

ALTER TABLE changes the structure of an existing table. MariaDB supports many actions in one statement:

SQL
ALTER TABLE products ADD COLUMN sku VARCHAR(20) NULL AFTER name;
ALTER TABLE products ADD COLUMN weight_g INT NOT NULL DEFAULT 0, ADD INDEX idx_stock (stock);
ALTER TABLE products MODIFY COLUMN name VARCHAR(150) NOT NULL;
ALTER TABLE products CHANGE COLUMN stock quantity INT NOT NULL DEFAULT 0;   -- rename + redefine
ALTER TABLE products RENAME COLUMN quantity TO in_stock;                    -- rename only
ALTER TABLE products DROP COLUMN weight_g;
ALTER TABLE products ALTER COLUMN price SET DEFAULT 1.00;
ALTER TABLE products ENGINE=InnoDB;                                          -- rebuild
RENAME TABLE products TO items;

Notes:

  • MODIFY repeats the whole column definition. Leave out NOT NULL or DEFAULT and you silently remove them. Always write the full definition.
  • AFTER column and FIRST position a new column. Column order rarely matters in queries but matters for readability.
  • Many alterations rebuild the table. On a big production table that can lock writes for a long time. MariaDB's ALGORITHM=INSTANT and ALGORITHM=INPLACE clauses make some changes (adding a column at the end, for example) nearly free; ask for them explicitly so MariaDB errors out instead of silently choosing a slow copy: ALTER TABLE t ADD COLUMN c INT, ALGORITHM=INSTANT;.
  • ADD COLUMN IF NOT EXISTS and DROP COLUMN IF EXISTS are MariaDB conveniences for repeatable migrations.

Runnable counterpart, using the parts SQLite supports (add, rename, drop columns):

SQL Change a table's structure

Press Run (or Ctrl + Enter) to see the result here.

TRUNCATE, DELETE and DROP

Three ways to get rid of data, with very different effects:

Statement Removes Structure kept Can roll back Resets AUTO_INCREMENT
DELETE FROM t Rows (one by one) Yes Yes (InnoDB) No
TRUNCATE TABLE t All rows (drops and recreates) Yes No (implicit commit) Yes
DROP TABLE t Rows and the table No No n/a
SQL
TRUNCATE TABLE products;          -- fast, empties it, no WHERE possible
DROP TABLE IF EXISTS products;
DROP TABLE a, b, c;               -- several at once

TRUNCATE cannot be used on a table referenced by a foreign key from another table (unless checks are disabled). DROP TEMPORARY TABLE removes only a temporary table and never touches a real one with the same name, which makes it the safer choice in scripts.

SQLite has no TRUNCATE; the same effect comes from DELETE FROM (and clearing its sequence counter):

SQL Empty and drop

Press Run (or Ctrl + Enter) to see the result here.

Good habits

  • Pick the narrowest type that fits, and NOT NULL unless "unknown" is a real answer.
  • Name keys and indexes explicitly (uq_products_name, idx_orders_customer).
  • Keep schema changes in numbered migration files, not typed ad hoc.
  • Test ALTER TABLE on a copy with production-sized data before running it for real.
flowchart LR
  A["CREATE TABLE"] --> B["INSERT data"]
  B --> C["ALTER TABLE as needs change"]
  C --> D["TRUNCATE: empty"]
  C --> E["DROP: remove"]

The usual life of a table.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which statement copies the exact structure of a table including keys, with no rows?

    CREATE TABLE ... LIKE copies columns, indexes and defaults. CREATE TABLE ... AS SELECT copies data but not the indexes.
  2. 2. What is the danger of ALTER TABLE ... MODIFY COLUMN name VARCHAR(150)?

    MODIFY replaces the full column definition, so every attribute you leave out disappears.
  3. 3. How does TRUNCATE TABLE differ from DELETE FROM table?

    TRUNCATE recreates the table, causing an implicit commit. DELETE removes rows individually and can be rolled back in InnoDB.
  4. 4. Which clause asks MariaDB to fail instead of doing a slow table copy?

    Naming the algorithm makes the statement error out if that method is not possible.

Summary

  • CREATE TABLE defines columns, keys, engine and charset; LIKE and AS SELECT copy tables.
  • SHOW TABLES, DESCRIBE and SHOW CREATE TABLE inspect them.
  • ALTER TABLE adds, changes, renames and drops columns; MODIFY must repeat the full definition.
  • DELETE removes rows and can be rolled back, TRUNCATE empties fast, DROP removes the table.

Next: INSERT, UPDATE and DELETE