#1 India's Top IT Training Institute
×

About Uncodemy

Know more about who we are and what we stand for.

About Us

About Us

Discover our mission to equip individuals with essential IT skills and industry expertise.

Read More →
UnCodeMy Gallery

UnCodeMy Gallery

Explore highlights from our IT training sessions and success stories.

Read More →
Mentors

Mentors

Meet the industry experts who train and guide you through your course.

Read More →
Free Resources

Free Resources

Free tutorials, interview questions, tools and calculators to help you learn.

Explore →
×

Free Resources

Learn, practice and plan your career — bilkul free.

Tutorials

Tutorials

Step-by-step written tutorials to learn any technology at your own pace.

Read More →
Free Counselling

Free Counselling

Book a free video counselling session and pick the right course for you.

Read More →
Online Compiler

Online Compiler

Write, run and test your code online — no setup required.

Read More →
MySQL Tutorial · Data Analytics

How to Create a Table in MySQL: Syntax and Best Practices

The short answer: CREATE TABLE table_name (column_name data_type constraints, ...); run inside a selected database. The longer answer is about the choices you make on each line, because a table is hard to change once real data and application code depend on it.

CREATE TABLE table_name (
  column1 datatype [NOT NULL] [DEFAULT value],
  column2 datatype ...,
  PRIMARY KEY (column1),
  [UNIQUE KEY name (column)],
  [CONSTRAINT name FOREIGN KEY (col) REFERENCES other_table(col)]
) [ENGINE=InnoDB] [DEFAULT CHARSET=utf8mb4];

If you have not created a database yet, start with creating a database in MySQL and run USE your_database; first.

A complete example: a products table

CREATE TABLE IF NOT EXISTS products (
  product_id   INT UNSIGNED NOT NULL AUTO_INCREMENT,
  sku          VARCHAR(20)  NOT NULL,
  name         VARCHAR(120) NOT NULL,
  category     VARCHAR(40)  NOT NULL DEFAULT 'General',
  price        DECIMAL(10,2) NOT NULL,
  in_stock     BOOLEAN      NOT NULL DEFAULT TRUE,
  created_at   DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (product_id),
  UNIQUE KEY uq_products_sku (sku)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Line by line, here is why each choice was made:

Insert rows and check the result

INSERT INTO products (sku, name, price) VALUES ('KB-101','USB keyboard',649.00);
INSERT INTO products (sku, name, category, price, in_stock)
VALUES ('WC-220','HD webcam','Video',1899.00,FALSE);

SELECT product_id, sku, name, category, price, in_stock FROM products;
product_idskunamecategorypricein_stock
1KB-101USB keyboardGeneral649.001
2WC-220HD webcamVideo1899.000

The first row picked up category = 'General' and in_stock = 1 from the defaults. BOOLEAN is stored as TINYINT(1), so it shows 1 and 0. DESCRIBE confirms what MySQL actually built:

DESCRIBE products;
FieldTypeNullKeyDefaultExtra
product_idint unsignedNOPRINULLauto_increment
skuvarchar(20)NOUNINULL
namevarchar(120)NONULL
categoryvarchar(40)NOGeneral
pricedecimal(10,2)NONULL
in_stocktinyint(1)NO1
created_atdatetimeNOCURRENT_TIMESTAMPDEFAULT_GENERATED

Adding rules: CHECK and FOREIGN KEY

A second table can reference the first and validate its own values:

CREATE TABLE product_reviews (
  review_id  INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  product_id INT UNSIGNED NOT NULL,
  rating     TINYINT NOT NULL,
  CONSTRAINT chk_rating CHECK (rating BETWEEN 1 AND 5),
  CONSTRAINT fk_reviews_product FOREIGN KEY (product_id)
    REFERENCES products(product_id)
) ENGINE=InnoDB;

INSERT INTO product_reviews (product_id, rating) VALUES (1, 7);
ERROR 3819 (HY000): Check constraint 'chk_rating' is violated.

The foreign key column must have the same type as the column it references, including UNSIGNED. A mismatch is a common reason the CREATE TABLE itself fails. CHECK constraints are enforced from MySQL 8.0.16; older versions parsed them and silently ignored them. Every rule is covered with examples in MySQL constraints explained.

Copying a table: LIKE vs AS SELECT

CREATE TABLE products_copy LIKE products;               -- structure only, keys included
CREATE TABLE products_snapshot AS SELECT * FROM products; -- data, but no keys
SHOW CREATE TABLE products_snapshot\G
CREATE TABLE `products_snapshot` (
  `product_id` int unsigned NOT NULL DEFAULT '0',
  `sku` varchar(20) NOT NULL,
  `name` varchar(120) NOT NULL,
  `category` varchar(40) NOT NULL DEFAULT 'General',
  `price` decimal(10,2) NOT NULL,
  `in_stock` tinyint(1) NOT NULL DEFAULT '1',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

Look closely: the snapshot has no PRIMARY KEY, no UNIQUE KEY and no AUTO_INCREMENT. CREATE TABLE ... AS SELECT copies columns and data only. Use LIKE followed by INSERT ... SELECT when you need a true copy.

Mistakes that cause errors or regret later

To change a table after creating it, use ALTER TABLE (add a column, add an index, change a type). To remove it, use DROP TABLE. The differences between deleting data and dropping objects are explained in DELETE vs TRUNCATE vs DROP.

Become a Job-Ready Data Analyst

Master MySQL, Advanced Excel, Power BI, Tableau and Python with Uncodemy's Data Analytics Training in Noida — live projects, expert mentors and Dedicated Placement Support.

Enroll in Data Analytics Course →