# Table Creation
# Table creation with Primary Key
CREATE TABLE Person ( PersonID INT UNSIGNED NOT NULL, LastName VARCHAR(66) NOT NULL, FirstName VARCHAR(66), Address VARCHAR(255), City VARCHAR(66), PRIMARY KEY (PersonID) );
A primary key is a
NOT NULL single or a multi-column identifier which uniquely identifies a row of a table. An index (opens new window) is created, and if not explicitly declared as
NOT NULL, MySQL will declare them so silently and implicitly.
A table can have only one
PRIMARY KEY, and each table is recommended to have one. InnoDB will automatically create one in its absence, (as seen in MySQL documentation (opens new window)) though this is less desirable.
INT also known as "surrogate key", is used for thin index optimization and relations with other tables. This value will (normally) increase by 1 whenever a new record is added, starting from a default value of 1.
However, despite its name, it is not its purpose to guarantee that values are incremental, merely that they are sequential and unique.
INT value will not reset to its default start value if all rows in the table are deleted, unless the table is truncated using
TRUNCATE TABLE (opens new window) statement.
# Defining one column as Primary Key (inline definition)
If the primary key consists of a single column, the
PRIMARY KEY clause can be placed inline with the column definition:
CREATE TABLE Person ( PersonID INT UNSIGNED NOT NULL PRIMARY KEY, LastName VARCHAR(66) NOT NULL, FirstName VARCHAR(66), Address VARCHAR(255), City VARCHAR(66) );
This form of the command is shorter and easier to read.
# Defining a multiple-column Primary Key
It is also possible to define a primary key comprising more than one column. This might be done e.g. on the child table of a foreign-key relationship. A multi-column primary key is defined by listing the participating columns in a separate
PRIMARY KEY clause. Inline syntax is not permitted here, as only one column may be declared
PRIMARY KEY inline. For example:
CREATE TABLE invoice_line_items ( LineNum SMALLINT UNSIGNED NOT NULL, InvoiceNum INT UNSIGNED NOT NULL, -- Other columns go here PRIMARY KEY (InvoiceNum, LineNum), FOREIGN KEY (InvoiceNum) REFERENCES -- references to an attribute of a table );
Note that the columns of the primary key should be specified in logical sort order, which may be different from the order in which the columns were defined, as in the example above.
Larger indexes require more disk space, memory, and I/O. Therefore keys should be as small as possible (especially regarding composed keys). In InnoDB, every 'secondary index' includes a copy of the columns of the
# Basic table creation
CREATE TABLE statement is used to create a table in a MySQL database.
CREATE TABLE Person ( `PersonID` INTEGER NOT NULL PRIMARY KEY, `LastName` VARCHAR(80), `FirstName` VARCHAR(80), `Address` TEXT, `City` VARCHAR(100) ) Engine=InnoDB;
Every field definition must have:
- Field name: A valid field Name. Make sure to encolse the names in `-chars. This ensures that you can use eg space-chars in the fieldname.
- Data type [Length]: If the field is
VARCHAR, it is mandatory to specify a field length.
NOT NULL: If
NOT NULLis specified, then any attempt to store a
NULLvalue in that field will fail.
- See more on data types and their attributes here (opens new window).
Engine=... is an optional parameter used to specify the table's storage engine.
If no storage engine is specified, the table will be created using the server's default table storage engine (usually InnoDB or MyISAM).
# Setting defaults
Additionally, where it makes sense you can set a default value for each field by using
CREATE TABLE Address ( `AddressID` INTEGER NOT NULL PRIMARY KEY, `Street` VARCHAR(80), `City` VARCHAR(80), `Country` VARCHAR(80) DEFAULT "United States", `Active` BOOLEAN DEFAULT 1, ) Engine=InnoDB;
If during inserts no
Street is specified, that field will be
NULL when retrieved. When no
Country is specified upon insert, it will default to "United States".
You can set default values for all column types, except (opens new window) for
# Table creation with Foreign Key
CREATE TABLE Account ( AccountID INT UNSIGNED NOT NULL, AccountNo INT UNSIGNED NOT NULL, PersonID INT UNSIGNED, PRIMARY KEY (AccountID), FOREIGN KEY (PersonID) REFERENCES Person (PersonID) ) ENGINE=InnoDB;
Foreign key: A Foreign Key (
FK) is either a single column, or multi-column composite of columns, in a referencing table. This
FK is confirmed to exist in the referenced table. It is highly recommended that the referenced table key confirming the
FK be a Primary Key, but that is not enforced. It is used as a fast-lookup into the referenced where it does not need to be unique, and in fact can be a left-most index there.
Foreign key relationships involve a parent table that holds the central data values, and a child table with identical values pointing back to its parent. The FOREIGN KEY clause is specified in the child table. The parent and child tables must use the same storage engine. They must not be TEMPORARY (opens new window) tables.
Corresponding columns in the foreign key and the referenced key must have similar data types. The size and sign of integer types must be the same. The length of string types need not be the same. For nonbinary (character) string columns, the character set and collation must be the same.
Note: foreign-key constraints are supported under the InnoDB storage engine (not MyISAM or MEMORY). DB set-ups using other engines will accept this
CREATE TABLE statement but will not respect foreign-key constraints. (Although newer MySQL versions default to
InnoDB, but it is good practice to be explicit.)
# Show Table Structure
If you want to see the schema information of your table, you can use one of the following:
SHOW CREATE TABLE child; -- Option 1 CREATE TABLE `child` ( `id` int(11) NOT NULL AUTO_INCREMENT, `fullName` varchar(100) NOT NULL, `myParent` int(11) NOT NULL, PRIMARY KEY (`id`), KEY `mommy_daddy` (`myParent`), CONSTRAINT `mommy_daddy` FOREIGN KEY (`myParent`) REFERENCES `parent` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
If used from the mysql commandline tool, this is less verbose:
SHOW CREATE TABLE child \G
A less descriptive way of showing the table structure:
mysql> CREATE TABLE Tab1(id int, name varchar(30)); Query OK, 0 rows affected (0.03 sec) mysql> DESCRIBE Tab1; -- Option 2 +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | varchar(30) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+
Both DESCRIBE and DESC gives the same result.
DESCRIBE performed on all tables in a database at once, see this Example (opens new window).
# Cloning an existing table
A table can be replicated as follows:
CREATE TABLE ClonedPersons LIKE Persons;
The new table will have exactly the same structure as the original table, including indexes and column attributes.
As well as manually creating a table, it is also possible to create table by selecting data from another table:
CREATE TABLE ClonedPersons SELECT * FROM Persons;
You can use any of the normal features of a
SELECT statement to modify the data as you go:
CREATE TABLE ModifiedPersons SELECT PersonID, FirstName + LastName AS FullName FROM Persons WHERE LastName IS NOT NULL;
Primary keys and indexes will not be preserved when creating tables from
SELECT. You must redeclare them:
CREATE TABLE ModifiedPersons (PRIMARY KEY (PersonID)) SELECT PersonID, FirstName + LastName AS FullName FROM Persons WHERE LastName IS NOT NULL;
# CREATE TABLE FROM SELECT
You can create one table from another by adding a
SELECT statement at the end of the
CREATE TABLE statement:
CREATE TABLE stack ( id_user INT, username VARCHAR(30), password VARCHAR(30) );
Create a table in the same database:
-- create a table from another table in the same database with all attributes CREATE TABLE stack2 AS SELECT * FROM stack; -- create a table from another table in the same database with some attributes CREATE TABLE stack3 AS SELECT username, password FROM stack;
Create tables from different databases:
-- create a table from another table from another database with all attributes CREATE TABLE stack2 AS SELECT * FROM second_db.stack; -- create a table from another table from another database with some attributes CREATE TABLE stack3 AS SELECT username, password FROM second_db.stack;
To create a table same of another table that exist in another database, you need to specifies the name of the database like this:
# Table Create With TimeStamp Column To Show Last Update
The TIMESTAMP column will show when the row was last updated.
CREATE TABLE `TestLastUpdate` ( `ID` INT NULL, `Name` VARCHAR(50) NULL, `Address` VARCHAR(50) NULL, `LastUpdate` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) COMMENT='Last Update' ;
CREATE TABLE table_name ( column_name1 data_type(size), column_name2 data_type(size), column_name3 data_type(size), .... ); // Basic table creation
CREATE TABLE table_name [IF NOT EXISTS] ( column_name1 data_type(size), column_name2 data_type(size), column_name3 data_type(size), .... ); // Table creation checking existing
CREATE [TEMPORARY] TABLE table_name [IF NOT EXISTS] ( column_name1 data_type(size), column_name2 data_type(size), column_name3 data_type(size), .... ); // Temporary table creation
CREATE TABLE statement should end with an
CREATE TABLE table_name ( column_definitions ) ENGINE=engine;
Some options are:
InnoDB: (Default since version 5.5.5) It's a transation-safe (ACID compliant) engine. It has transaction commit and roll-back, and crash-recovery capabilities and row-level locking.
MyISAM: (Default before version 5.5.5) It's a plain-fast engine. It doesn't support transactions, nor foreign keys, but it's useful for data-warehousing.
Memory: Stores all data in RAM for extremely fast operations but table date will be lost on database restart.
More engine options here (opens new window).