SQL Create Table

In this tutorial, you will learn how to create tables within a database using SQL.

Establishing a table

In the previous chapter, we learned how to create a database on the database server. Now it's time to create some tables in the database, which will actually save data. A database table is just a way to organize information into rows and columns.

The SQL CREATE TABLE statement is used to create tables.

syntax

The basic syntax for creating a table can be given in the following way:

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

To easily understand this syntax, let's takedemonstrationTo create a table in the database, enter the following statement in the MySQL command-line tool and then press Enter:

--　MySQL database syntax
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
);
--　SQL Server database syntax
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 above statement creates a table namedpersonof the table, which hasid,name,birth_dateandphonefour columns. Note that there is a data type declaration after each column name; this declaration specifies what type of data the column will store, such as integers, strings, dates, etc.

certaindata typecan be declared using the length parameter, which indicates how many characters can be stored in the column. For example, VARCHAR(50) can accommodate up to50 characters.

Column data types may vary depending on the database system. For example, MySQL and SQL Server support INT integer data types, while Oracle databases support NUMBER data types.

Please see the reference sectionSQL DB data types, to get information about all availabledata type) details.

In the previous statement, some other constraints (also known asmodifier).Constraint defines the rules about the allowed values in the column.

• NOT NULL constraint ensures that the field cannot accept a NULL value.

• PRIMARY KEY constraint marks the corresponding field as the primary key of the table.

• AUTO_INCREMENT attribute is a standard SQL extension for MySQL, which tells MySQL that if the value is not specified, it will increase the previous value by1to automatically assign a value to this field. It is only applicable to numeric fields.

• UNIQUE constraints ensure that each row of a column must have a unique value.

In the next chapter, we will learn aboutSQL constraintsMore information.

Create Table If Not Exists

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

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
);