Custom Search
 


Create MySQL table by using CREATE TABLE statement


This tutorial demonstrates how to create a MySQL table by using the basic CREATE TABLE syntax. To view the full syntax (which is daunting) and explanation, visit MySQL website for the version that you use, such as version 5.7 CREATE TABLE Syntax.

Below is the basic, nice and easy syntax of MySQL create table statement.

CREATE TABLE [IF NOT EXISTS] table_name
(
column1 ...,
column2 ...,
...
) engine=table_type

Without too much explanation for the syntax, we'll look at a practical example as usual.

This example demonstrates the most commonly used attributes when creating a table by SQL. After completing this tutorial, you should be able to apply the syntax to create your own table without much modifications.

We are going to create 2 tables - categories and products. These 2 tables are related in the sense that CategoryID from categories table is a foreign key in products table. This way, we are able to demonstrate the foreign key creation syntax which is a very important and useful attribute of the CREATE TABLE statement.

Creating table categories

Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.

CREATE TABLE IF NOT EXISTS `categories` 
(
`CategoryID` tinyint(5) unsigned NOT NULL AUTO_INCREMENT,
`CategoryName` varchar(15) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`Description` mediumtext CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
`Picture` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
PRIMARY KEY (`CategoryID`),
UNIQUE KEY `Uidx_categories_category_name` (`CategoryName`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;

The script above creates the categories table in MySQL Northwind database.

  1. IF NOT EXISTS

    IF NOT EXISTS is an optional part of the statement that allows you to check if the table you are creating already exists in the database. If this is the case, MySQL will ignore the whole statement and it will not create any new table. It is highly recommended that you use IF NOT EXISTS in every CREATE TABLE statement to prevent your script from generating an error if the table already exists.

  2. How to use the punctuation mark ` in the statement.

    The punctuation mark ` is commonly referred as backquote, back quote, acute, backtick, back tick, left quote. It can be used to surround a MySQL identifier (e.g. table name, column name, etc.) in the statement so that reserved words can be used as identifiers and MySQL database engine won't complain. Even though you have the capability to do so, you should always avoid using MySQL reserved words as identifiers as it'll cause hard-to-detect bugs, problems, and headache later for development and maintenance.

    The rule of thumb is that (1) never use MySQL reserved words as identifiers. Use common sense to avoid them, and if you are not sure, check the reserved words in MySQL before use. The reserved words are slight different in each MySQL version. (2) leave all identifiers as they are with no backticks surrounding them. If you are not certain if an identifier is a reserved word, just run your SQL in the development environment. The script will fail if the identifier is a reserved word, then obviously you need to change it to something else.

    Note that the backtick is used for every identifier in the 2 examples of this tutorial, this is because the scripts are chosen from the MySQL Northwind database creation script. The entire script was generated by SQLyog automatically. When hand writing SQL CREATE TABLE script, you may not want to use backticks surrounding identifier names.

    To learn more about identifiers, refer to MySQL page Schema Object Names.

  3. The first column CategoryID

    • CategoryID is defined as a TINYINT datatype which is an integer with maximum 5 digits in length.

    • Unsigned means this column can't store negative values.

    • The NOT NULL keyword indicates that CategoryID column cannot contain NULL values.

    • The AUTO_INCREMENT function instructs that the value in CategoryID column is automatically incremented when new records are inserted into the table. The initial value will start at 1 and increment by 1 with each insert, unless otherwise specified. Note that there can only be one column in CREATE TABLE statement that is set to AUTO_INCREMENT and this column must be an integer date type and must be the primary key of the table.

  4. The second column CategoryName

    • CategoryName is defined as a VARCHAR datatype which allows text values and maximum length of the text value is 15 characters.

    • Next 2 attributes are CHARACTER SET and COLLATE. MySQL includes character set support that enables you to store data using a variety of character sets and perform comparisons according to a variety of collations. For details, see Character Set Support for the relevant MySQL version.

    • NOT NULL enforces that the column cannot contain NULL values.

    • But DEFAULT constraint allows empty text value to be added if no value is provided. DEFAULT means that if no value is provided when inserting a new record, a default empty value '' will be inserted.

  5. The third column Description and fourth column Picture have similar attributes as the second column.

    MySQL supports 4 TEXT field types - TINYTEXT, TEXT, MEDIUMTEXT and LONGTEXT.

    The maximum length of each type is as follows:

    TINYTEXT - 256 bytes
    TEXT - about 64kb
    MEDIUMTEXT - about 16MB
    LONGTEXT - about 4GB

  6. The fourth column Picture has similar attributes as the second column.
  7. PRIMARY KEY (`CategoryID`)

    This attribute defines the primary key column for the table and there is no specific key name defined. Note that if you don't specify a name for the primary key, MySQL will automatically create one for you in the background.

    Here is an extract from MySQL manual.

    A PRIMARY KEY is a unique index where all key columns must be defined as NOT NULL. If they are not explicitly declared as NOT NULL, MySQL declares them so implicitly (and silently). A table can have only one PRIMARY KEY.
  8. UNIQUE KEY `Uidx_categories_category_name` (`CategoryName`)

    UNIQUE KEY attribute has a name Uidx_categories_category_name and is defined on column CategoryName.

    KEY is normally a synonym for INDEX.

  9. ENGINE=InnoDB

    This attribute specifies that the storage engine type for the table is InnoDB. InnoDB tables are transaction-safe with row locking and foreign keys enforcement capability.

    MySQL has these storage engines available - InnoDB, MyISAM, HEAP, MERGE, MEMORY, EXAMPLE, CSV, ARCHIVE, FEDERATED, and NDBCLUSTER.

    If no storage engine explicitly specified, MySQL will use InnoDB by default.

    Since MySQL 5.5.5, the default storage engine for new tables is InnoDB. This change applies to newly created tables that don't specify a storage engine with a clause. The InnoDB table type brings many benefits, including ACID transaction, enforcing referential integrity, table and index caching, checksum mechanism, query optimisation, and crash recovery.

  10. DEFAULT CHARSET=utf8

    This attribute is the default charset defined at the table level. If a column does not have a charset defined, the default table level charset will be used.

Creating table products

Here is the second CREATE TABLE statement for the second table products. We'll only explain the attributes if they haven't been mentioned above in the categories CREATE TABLE statement.

CREATE TABLE `products` (
`ProductID` int(10) unsigned NOT NULL AUTO_INCREMENT,
`ProductName` varchar(40) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`SupplierID` int(10) unsigned NOT NULL,
`CategoryID` tinyint(5) unsigned NOT NULL,
`QuantityPerUnit` varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`UnitPrice` double NOT NULL DEFAULT '0',
`UnitsInStock` smallint(5) unsigned NOT NULL DEFAULT '0',
`UnitsOnOrder` smallint(5) unsigned NOT NULL DEFAULT '0',
`ReorderLevel` smallint(5) unsigned NOT NULL DEFAULT '0',
`Discontinued` enum('y','n') CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT 'n',
PRIMARY KEY (`ProductID`),
KEY `FK_products_categoryid` (`CategoryID`),
KEY `FK_products_supplierid` (`SupplierID`),
KEY `idx_products_product_name` (`ProductName`),
CONSTRAINT `FK_products_categoryid` FOREIGN KEY (`CategoryID`) REFERENCES `categories` (`CategoryID`),
CONSTRAINT `FK_products_supplierid` FOREIGN KEY (`SupplierID`) REFERENCES `suppliers` (`SupplierID`)
) ENGINE=InnoDB AUTO_INCREMENT=78 DEFAULT CHARSET=utf8;

  1. Discontinued column

    This column has a data type enum('y','n'). As stated in MySQL manual, an ENUM is a string object with a value chosen from a list of permitted values that are enumerated explicitly in the column specification at table creation time. An enumeration value must be a quoted string literal; it may not be an expression, even one that evaluates to a string value. Here in this example, the Discontinued column can only contain the value y or n, and the default is n (see the last attribute).

  2. KEY `FK_products_categoryid` (`CategoryID`)

    This keyword KEY is normally a synonym for INDEX, so here an index is created on column CategoryID and the name of the index is FK_products_categoryid. The prefix FK indicates that this index is created on a column that will be used as a foreign key.

  3. KEY `FK_products_supplierid` (`SupplierID`),

    Again, an index is created on SupplierID column with an index name FK_products_supplierid.

  4. KEY `idx_products_product_name` (`ProductName`)

    Again, an index is created on ProductName column with an index name idx_products_product_name. The prefix idx indicates that this is an index.

  5. CONSTRAINT `FK_products_categoryid` FOREIGN KEY (`CategoryID`) REFERENCES `categories` (`CategoryID`)

    This line of statement specifies that column CategoryID in this table references column CategoryID in categories table. Note that MySQL requires that indexes must exist on foreign keys and referenced keys so that foreign key checks can be super fast and does not require a table scan.

    In our example here, the foreign keys are on the referencing table products, and referenced keys are on the referenced table categories.

    MySQL manual states:

    MySQL supports foreign keys, which let you cross-reference related data across tables, and foreign key constraints, which help keep this spread-out data consistent.

    IMPORTANT:

    In MySQL, only InnoDB tables support checking of foreign key constraints. For other storage engines, MySQL Server parses and ignores the FOREIGN KEY and REFERENCES syntax in CREATE TABLE statements.

  6. CONSTRAINT `FK_products_supplierid` FOREIGN KEY (`SupplierID`) REFERENCES `suppliers` (`SupplierID`)

    Same as above, a foreign key is created on SupplierID in products table which references SupplierID in suppliers table.

This tutorial has provided in-depth information which I hope may help you speed up your learning curve. By now, you have equipped with enough knowledge and I reckon the best way to learn about CREATE TABLE statement is practice - writing your own scripts. Before making your hands dirty, here is a checklist of common attributes in the CREATE TABLE statement for your quick reference.

  • Data type and size
  • Primary Key definition
  • AUTO_INCREMENT attribute for primary key
  • NULL and NOT NULL
  • Foreign Key references
  • UNIQUE Key
  • Indexes
  • Enum
  • Default values
  • CHARACTER SET
  • COLLATE
  • Engine type

Happy Coding!



Other tutorials in this category

1. Create MySQL table by using another table

2. Create MySQL temporary table

3. How to disable (or enable) Safe Updates in MySQL

4. MySQL constraints explained by examples

Back to Tutorial Index Page


Copyright © 2024 GeeksEngine.com. All Rights Reserved.

This website is hosted by HostGator.

No portion may be reproduced without my written permission. Software and hardware names mentioned on this site are registered trademarks of their respective companies. Should any right be infringed, it is totally unintentional. Drop me an email and I will promptly and gladly rectify it.

 
Home | Feedback | Terms of Use | Privacy Policy