SQL CREATE TABLE
SQL > Data Definition Language (DDL) >
Create Table Statement
In a relational database, data is stored in tables. Given that there is no way for the database vendor to know ahead of time what your data storage needs are, you will for sure need to create tables that fit your needs in the database. Therefore, the CREATE TABLE statement is one of the most fundamental components of SQL.
Before we dive into the SQL syntax for CREATE TABLE, it is a good idea to understand what goes into a table. A table is made up of rows and columns. Each row represents one piece of data, and each column can be thought of as representing a component of that piece of data. For example, if we have a table for recording customer information, then the columns may include information such as First Name, Last Name, Address, City, Country, and Birth Date. As a result, when we specify a table, we include the column headers and the type of data for each column. We may also decide to place certain limitations, or constraints, to guarantee that the data stored in the table makes sense.
The SQL syntax for CREATE TABLE is
CREATE TABLE "table_name"
("column 1" "data type for column 1" [column 1 constraint(s)],
"column 2" "data type for column 2" [column 2 constraint(s)],
[table constraint(s)] );
"Column 1" and "column 2" represent the name of each column. After the name, we specify the data type for that column. Typical data types include integers (such as 1), real numbers (such as 0.55), strings (such as 'sql'), date/time expressions (such as '2000-JAN-25 03:22:22'), and binary types. Different relational databases allow for different data types, so please consult with a database-specific reference first.
The [ ] symbol means that the phrase inside it may occur zero, one, or more times. When creating a table, specifying column and table constraints is optional, and each column and each table may have more than one constraint.
Six types of constraints can be placed when creating a table:
To create a customer table with the fields specified in the second paragraph above, we would type in
CREATE TABLE Customer
No constraints were specified in the above SQL statement. What if we want to add a constraint that says the default country is 'United States'? In other words, if no data is entered for the "Country" column, it would be set to 'United States.' In that case, we would type the following SQL:
CREATE TABLE Customer
Country char(25) default 'United States',
CREATE TABLE AS
Sometimes the table structure we need is the same as another table in the database. In this case, we can also use CREATE TABLE to make a copy of the table structure so we do not need to type all the column names, data types, and constraints in detail. The syntax for doing this is:
CREATE TABLE "table_name" AS
To copy both the structure and data of Table1 into Table2, we would issue the following SQL statement:
CREATE TABLE Table2 AS SELECT * FROM Table1;
To copy the structure of Table1 into Table2 without any data, we would issue the following SQL statement:
CREATE TABLE Table2 AS SELECT * FROM Table1
WHERE 0 = 1;
The WHERE 0 = 1 clause is always false. Therefore, no row of data will be copied from Table1 to Table2. Only the table structure is copied over.
CREATE TABLE IF NOT EXISTS
In MySQL, SparkSQL, and Hive, there is an option to include an "IF NOT EXISTS" clause right after CREATE TABLE. This will tell the system to only create the table only if that table is not already present. If the table already exists, this statement will be ignored.
The IF NOT EXISTS clause can be used to create a table from scratch or to create a table using the structure of another table. The following two SQL statements are both valid:
CREATE TABLE IF NOT EXISTS Customer
CREATE TABLE IF NOT EXISTS Table2 AS SELECT * FROM Table1;
Note that IF NOT EXISTS is not available in Oracle or SQL Server.