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:

  • NOT NULL Constraint: Ensures that a column cannot have NULL value.
  • DEFAULT Constraint: Provides a default value for a column when none is specified.
  • UNIQUE Constraint: Ensures that all values in a column are different.
  • CHECK Constraint: Makes sure that all values in a column satisfy certain criteria.
  • Primary Key Constraint: Used to uniquely identify a row in the table.
  • Foreign Key Constraint: Used to ensure referential integrity of the data.

    • To create a customer table with the fields specified in the second paragraph above, we would type in

    CREATE TABLE Customer
    (First_Name char(50),
    Last_Name char(50),
    Address char(50),
    City char(50),
    Country char(25),
    Birth_Date datetime);

    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
    (First_Name char(50),
    Last_Name char(50),
    Address char(50),
    City char(50),
    Country char(25) default 'United States',
    Birth_Date datetime);

    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
    [SQL Statement];

    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
    (First_Name char(50),
    Last_Name char(50),
    Address char(50),
    City char(50),
    Country char(25),
    Birth_Date datetime);

    CREATE TABLE IF NOT EXISTS Table2 AS
    SELECT * FROM Table1;

    Note that IF NOT EXISTS is not available in Oracle or SQL Server.

    Next: SQL Data Types

    This page was last updated on October 11, 2024.



    Copyright © 2024   1keydata.com   All Rights Reserved     Privacy Policy     About   Contact