How to CREATE TABLE in MariaDB

Galaxy Glossary

How do I use CREATE TABLE in MariaDB with primary keys and foreign keys?

CREATE TABLE defines a new table, its columns, constraints, and storage engine in MariaDB.

Sign up for the latest in SQL knowledge from the Galaxy Team!

Description

Why use CREATE TABLE?

CREATE TABLE builds a new, permanent table in your database so you can store and query structured data. It lets you specify column names, data types, indexes, and constraints in one statement.

What is the basic syntax?

Use CREATE TABLE table_name (column_definitions, table_constraints) ENGINE=InnoDB; Add AUTO_INCREMENT for surrogate keys and PRIMARY KEY for uniqueness.

How do I create a table with constraints?

Include PRIMARY KEY, UNIQUE, CHECK, and FOREIGN KEY clauses inside the parentheses.Constraints enforce data integrity automatically.

Can I copy an existing table structure?

Yes. CREATE TABLE new_table LIKE existing_table; duplicates the schema without data. Useful for staging or archiving.

How do I create temporary tables?

Prefix with TEMPORARY. CREATE TEMPORARY TABLE temp_orders (...); The table exists only for the current session and is dropped automatically.

Best practices to follow

1) Always add a primary key. 2) Use NOT NULL on mandatory columns. 3) Pick the smallest data type that fits.4) Document tables with comments for future maintainers.

Example: Building an e-commerce schema

The syntax and example below show how to build Customers, Orders, Products, and OrderItems with strong referential integrity.

.

Why How to CREATE TABLE in MariaDB is important

How to CREATE TABLE in MariaDB Example Usage


-- Create a table to capture returns for each order item
CREATE TABLE OrderItemReturns (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    order_item_id INT UNSIGNED NOT NULL,
    return_date DATE NOT NULL,
    reason VARCHAR(255),
    FOREIGN KEY (order_item_id) REFERENCES OrderItems(id)
        ON DELETE CASCADE
) ENGINE=InnoDB;

How to CREATE TABLE in MariaDB Syntax


CREATE TABLE Customers (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE Products (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    price DECIMAL(10,2) NOT NULL CHECK (price >= 0),
    stock INT NOT NULL DEFAULT 0
) ENGINE=InnoDB;

CREATE TABLE Orders (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    customer_id INT UNSIGNED NOT NULL,
    order_date DATE NOT NULL,
    total_amount DECIMAL(12,2) NOT NULL,
    FOREIGN KEY (customer_id) REFERENCES Customers(id)
        ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE OrderItems (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    order_id INT UNSIGNED NOT NULL,
    product_id INT UNSIGNED NOT NULL,
    quantity SMALLINT NOT NULL,
    FOREIGN KEY (order_id) REFERENCES Orders(id)
        ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES Products(id)
        ON DELETE RESTRICT
) ENGINE=InnoDB;

Common Mistakes

Omitting a PRIMARY KEY. Without it, rows cannot be uniquely identified, causing update and delete anomalies. Always define a primary key on one or more columns.
Forgetting ON DELETE rules on foreign keys. The default RESTRICT may block deletes unexpectedly. Specify CASCADE or SET NULL when appropriate.

Frequently Asked Questions (FAQs)

Does column order matter?

No, but grouping frequently queried columns together can improve cache performance and readability.

How do I add a column later?

Use ALTER TABLE table_name ADD COLUMN new_col datatype AFTER existing_col; The operation is online for InnoDB in most cases.

Want to learn about other SQL terms?

dbt tests

dbt tests are declarative assertions in dbt that automatically validate data quality during model builds.

dbt utils

dbt utils is a community-maintained dbt package that adds time-saving utility macros for analytics engineers, such as generating surrogate keys, safely unioning models, and automating date spines.

dbt Tutorial Glossary: Build, Test & Deploy SQL Models

dbt is an open-source framework that lets data teams transform raw warehouse tables into tested, documented, production-ready datasets using version-controlled SQL.

Trusted by top engineers on high-velocity teams

Assort Health

Curri

Rubie