0Basic table creation
suggest changeThe 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
CHAR
orVARCHAR
, it is mandatory to specify a field length. - Attributes
NULL
|NOT NULL
: IfNOT NULL
is specified, then any attempt to store aNULL
value 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.
Found a mistake? Have a question or improvement idea?
Let me know.
Table Of Contents