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',
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.