MySQL Table Creation Basic table creation
Example
The 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
CHARorVARCHAR, it is mandatory to specify a field length. - Attributes
NULL|NOT NULL: IfNOT NULLis specified, then any attempt to store aNULLvalue in that field will fail. - See more on data types and their attributes here.
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 DEFAULT:
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 for BLOB, TEXT, GEOMETRY, and JSON fields.
