SQL CREATE TABLE Statement

Previous Page Next Page

Creating a Table

In the previous section, we learned how to create a database on the database server. Now, let's proceed to create tables within our database to store our data. A database table organizes information into rows and columns.

The SQL CREATE TABLE statement is used for creating a table.

Syntax

Here's the basic syntax for creating a table:

CREATE TABLE table_name (
column1_name data_type constraints,
column2_name data_type constraints,
....
);

To grasp this syntax easily, let's create a table in our demo database. Enter the following statement in the MySQL command-line tool and press enter:

Example

Try this code »

-- Syntax for MySQL Database 
CREATE TABLE persons (
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
birth_date DATE,
phone VARCHAR(15) NOT NULL UNIQUE
);

-- Syntax for SQL Server Database 
CREATE TABLE persons (
id INT NOT NULL PRIMARY KEY IDENTITY(1,1),
name VARCHAR(50) NOT NULL,
birth_date DATE,
phone VARCHAR(15) NOT NULL UNIQUE
);

The statement above creates a table named persons with four columns: id, name, birth_date, and phone. Each column is followed by a declaration of its data type, specifying whether it stores integers, strings, dates, etc.

Some data types can include a length parameter indicating how many characters the column can hold. For instance, VARCHAR(50) can store up to 50 characters.

Note: Column data types can differ depending on the database system. For instance, MySQL and SQL Server use the INT data type for integers, while Oracle uses NUMBER.

The table below summarizes the most commonly used data types supported by MySQL.

Data Type	Description
`INT`	Stores numeric values ranging from -2147483648 to 2147483647.
`DECIMAL`	Stores decimal values with exact precision.
`CHAR`	Stores fixed-length strings with a maximum size of 255 characters.
`VARCHAR`	Stores variable-length strings with a maximum size of 65,535 characters.
`TEXT`	Stores strings with a maximum size of 65,535 characters.
`DATE`	Stores date values in the YYYY-MM-DD format.
`DATETIME`	Stores combined date/time values in the YYYY-MM-DD HH:MM:SS format.
`TIMESTAMP`	Stores timestamp values. `TIMESTAMP` values are stored as the number of seconds since the Unix epoch ('1970-01-01 00:00:01' UTC).

Please visit the SQL DB data types reference section for detailed information on data types available in popular RDBMS like MySQL, SQL Server, etc.

Additionally, there are several constraints (also known as modifiers) applied to table columns in the preceding statement. Constraints define rules regarding the allowed values in columns:

The NOT NULL constraint ensures that the field cannot contain a NULL value.
The PRIMARY KEY constraint designates the corresponding field as the primary key for the table.
The AUTO_INCREMENT attribute, specific to MySQL, automatically assigns a value to the field if left unspecified, incrementing from the previous value by 1. This attribute is only applicable to numeric fields.
The UNIQUE constraint ensures that each row in a column must have a unique value.

We will learn more in SQL constraints in the next chapter.

Note: In Microsoft SQL Server, the auto-increment feature is achieved using the IDENTITY property. By default, it is set to IDENTITY(1,1), where the seed or starting value is 1, and the incremental value is also 1.

Tip: To view the column information or structure of a table in MySQL and Oracle database, execute the command DESC table_name;. In SQL Server, use EXEC sp_columns table_name; (replace table_name with the actual table name).

Create Table If Not Exists

If you attempt to create a table that already exists in the database, an error message will be displayed. To prevent this in MySQL, you can use the optional clause IF NOT EXISTS as shown below:

Example

Try this code »

CREATE TABLE IF NOT EXISTS persons (
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
birth_date DATE,
phone VARCHAR(15) NOT NULL UNIQUE
);

Tip: To view the list of tables within the currently selected database, execute the SHOW TABLES; statement on the MySQL command line.

Previous Page Next Page