MariaDB Tutorial / Define a Table Structure with CREATE TABLE

CREATE TABLE defines a new table’s columns, data types, constraints, and keys. Successful execution alone does not prove that the table has the intended structure: inspect the actual definition with DESCRIBE and SHOW CREATE TABLE, then check a saved row.

Decide what each column represents

A schema is the definition of a data structure. Here, one row represents one product, and separate columns hold its identifier, name, category, price, and stock. Combining everything into a single text field would make calculations, sorting, and validation depend on unnecessary parsing. Compare the same inputs using Choose Numeric Types: Integers, DECIMAL, FLOAT, and DOUBLE for numeric range and precision and Choose String Types: CHAR, VARCHAR, and TEXT for string length and comparison rules.

Choose lengths from expected inputs and business requirements. A limit of 100 characters is an example, not a universally correct product-name policy. The string-type guide explains those length decisions. Whether a price or stock quantity may be negative is a business constraint beyond simply selecting a numeric type; the NULL, defaults, and constraints guide separates those responsibilities.

Check the connection and existing table names

Connect as course_user to the practice server prepared in the installation guides, with the shopcourse database available. The command-line client and SQL-file guide explains the connection and SOURCE command. Run these checks inside that connection, not in the operating-system shell:

SELECT VERSION() AS server_version, DATABASE() AS selected_database;
USE shopcourse;
SELECT DATABASE() AS selected_database;

The selected database should be shopcourse. Save a complete normal example as a UTF-8 .sql file and run it with SOURCE and its actual local path. Keep the intentionally failing statements separate: an error can stop a source file. Use a learning instance, not a connection containing production data.

Leave the existing products table intact. This exercise uses products_schema_lab, a separate practice name. Check whether it already exists:

SHOW TABLES LIKE 'products_schema_lab';

An empty result is expected before its first creation. If it exists, inspect its structure and data before deciding whether to reset it. Use lowercase names with underscores consistently and avoid reserved words. These naming conventions reduce confusion over operating-system and server settings; they do not change the rules for text values, which use single quotes in the example.

Run the complete table-creation example

USE shopcourse;
CREATE TABLE products_schema_lab (
  product_id INT NOT NULL AUTO_INCREMENT,
  product_name VARCHAR(100) NOT NULL,
  category VARCHAR(30) NOT NULL,
  price DECIMAL(10,2) NOT NULL,
  stock INT NOT NULL DEFAULT 0,
  PRIMARY KEY (product_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO products_schema_lab (product_name, category, price)
VALUES ('Notebook', 'Stationery', 3500.00);
SHOW TABLES LIKE 'products_schema_lab';
DESCRIBE products_schema_lab;
SHOW CREATE TABLE products_schema_lab;
SELECT product_id, product_name, price, stock FROM products_schema_lab;

Inside the CREATE TABLE parentheses, commas separate column definitions and table-level rules. There is no comma after the last definition. PRIMARY KEY (product_id) declares the row identifier, and AUTO_INCREMENT generates a number when an insert omits that identifier. The primary-key and auto-increment guide explains how these features differ.

A storage engine is the implementation that stores and processes a table’s data. This example explicitly uses InnoDB for transactional behavior and subsequent relationship exercises. The utf8mb4 character set and utf8mb4_unicode_ci collation make the text settings explicit; they are not a claim that every application should use the same comparison policy.

The INSERT supplies a name, category, and price while omitting the generated identifier and stock. On the fresh table, the first identifier is 1 and stock becomes its default, 0. Preserve every semicolon when saving the entire example as a file.

Inspect the stored definition and first row

A server may display an integer as int(11). That number is not a larger integer range or a storage size in bytes. Normalized output can differ across versions and SQL modes, so compare the definition’s meaning rather than requiring identical formatting.

SHOW CREATE TABLE is useful for checking or recording a structure, but its output does not back up the rows. Consult the official CREATE TABLE reference for syntax and default handling.

Understand what IF NOT EXISTS preserves

Creating a table with the same name again normally raises a table-already-exists error. IF NOT EXISTS can skip creation, but it does not transform an existing table to match the new declaration. Try this after the normal example:

CREATE TABLE IF NOT EXISTS products_schema_lab (only_column INT);
SHOW WARNINGS;
DESCRIBE products_schema_lab;

The original five product columns remain; they do not become one only_column. An actual structural change requires an appropriate migration, such as ALTER TABLE, with data-preservation planning. Do not drop a production table to resolve a mismatch. CREATE OR REPLACE TABLE can replace existing data structures and is also unsuitable as a casual duplicate-error workaround.

Diagnose errors and repeat the exercise safely

Before repeating the exercise, use SELECT DATABASE(); and SHOW CREATE TABLE table_name;, substituting each actual table name, to confirm that the targets are the disposable tables created here. Back up any data you need to keep. Only after verifying those targets, remove them with:

DROP TABLE shopcourse.products_schema_lab;

This removes both the named tables and their rows. A data-definition statement such as DROP TABLE is not undone by an ordinary ROLLBACK. You can then rerun the complete normal example. The existing products table from the first product-table exercise is deliberately outside this reset.