SQL CREATE TABLE

Summary: in this tutorial, you will learn how to use SQL CREATE TABLE statement to create new tables in a database.

Introducing to SQL CREATE TABLE statement

A table is a basic element of the database. A table is like a spreadsheet which contains columns and rows. A table is used to store data e.g., customers, vendors, products.

To create a new table you use SQL CREATE TABLE statement. The following illustrates the common syntax of the SQL CREATE TABLE statement:

CREATE TABLE table_name(
column_name1 data_type(data_length ),
column_name2 data_type(data_length ),
..
CONSTRAINTS constraint_name PRIMARY KEY|UNIQUE|...
)

Let’s examine the statement in greater detail:

• Table name: it is recommended that the name of the table should be as meaningful as possible. You should use the plural form of a noun to name a table, for example, products, orders, vendors...etc.
• Column name: like table name, you should use meaning column name. If the column name is longer, you can use an abbreviation such as ID for identity, No for the number.
• Column data type: you should choose the appropriate data type for each column in a table. The most common data types are text (VARCHAR, NVARCHAR), numeric (SMALLINT, INT, BIGINT), DATE, TIME, DATETIME, BLOB. Please refer the specific user manual of the database system you are working it to find the supporting data types.
• Size of the column: you have to specify the maximum length of data for each column. For example, if you store product name, try to imagine the maximum length that a product may contain.
• Constraint:  You can define PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL or CHECK constraint with CONSTRAINT keyword. The CONSTRAINT keyword is optional.

SQL CREATE TABLE Example

We are going to create a new table named Projects to store project data. The following is the statement to create the Projects table.

CREATE TABLE Projects (
  ProjectID int(11) NOT NULL AUTO_INCREMENT,
  Name varchar(255) NOT NULL,
  Description varchar(4000) DEFAULT NULL,
  BeginDate date NOT NULL,
  EndDate date DEFAULT NULL,
  PRIMARY KEY (ProjectID)
)Code language: PHP (php)

In the projects table:

• PorjectID: is the primary key defined by the PRIMARY KEY constraint. It is a NOT NULL and auto-increment column.
• Name: the name of the project with VARCHAR data type with 255 characters long. It is a NOT NULL column.
• Description: the description of the project with VARCHAR data type with 4000 characters long. It is a NULL-able column.
• BeginDate and EndDate: are to specify the project’s period. They have the same DATE data type. The BeginDate is a NOT NULL column, while the EndDate is NULL-able column.

In this tutorial, we’ve shown you how to use SQL CREATE TABLE statement to create new tables in the database.