SQL CREATE TABLE Syntax and Examples – The Complete Guide

Creating database tables in SQL is one of the most common tasks a developer or DBA does in a database. Learn how to create tables, what the syntax is, and see some examples in this article.

This guide applies to Oracle, SQL Server, MySQL, and PostgreSQL.

What Is The Create Table Command Used For?

The SQL CREATE TABLE command is used to create a database table.

It can be used to create different types of database tables, such as temporary tables. However, in this article, I’ll only cover the regular database table.

 

SQL Create Table Syntax

The syntax for the SQL create table statement is:

CREATE [schema_name.]table_name (
  column_name data_type [NULL | NOT NULL] [inline_constraint] [DEFAULT default_value],
  ...
  out_of_line_constraints
);

 

The parameters or values mentioned in this syntax are:

schema_name

This is the schema that the table will be created under. It needs to be followed by a period and then the table name. It’s optional.

table_name

This is the name of the table that you want to create.

It’s a good idea not to include spaces in the table name, to make development easier. I also recommend using singular table names (read more on singular vs plural here).

column_name

This is the name of each column that can be stored in the database table. It can also be up to 30 characters.

data_type

The data type that the column relates to. Many data types require brackets after the data type to specify the number of characters or digits. You can find a full list of data types on Oracle’s website.

NULL|NOT NULL

This is used to specify if the column values are allowed to be NULL or not.

• NULL means the column can be set to NULL. If you don’t specify the value when inserting, or specify the value of NULL, the statement won’t cause an error and the data will be inserted.
• NOT NULL means the column cannot be set to NULL. If you don’t specify the value when inserting, or specify the value of NULL, the statement will cause an error.

inline_constraint

You can define a constraint here on this column. This is what is called an “inline constraint”, because you define it on the same line as the column.

The difference between inline and out of line constraints is that you can’t define a constraint with multiple columns inline, and NOT NULL constraints can only be defined inline.

default_value

If you want to have a default value on this column when it is not specified during an INSERT statement, you can specify it here. An example of this would be a created_date column – it doesn’t need to have its value specified when you insert it, so the default could be SYSDATE.

out_of_line_constraints

Here, you can specify table constraints, which are called out of line constraints. They work in the same way as inline constraints, except you can’t specify NOT NULL constraints here. For example, you may want to add a primary key or foreign key here.

 

Now, there are a whole lot of parameters that are not mentioned here, because they are not relevant for most database users.

For the complete syntax for the CREATE TABLE statement, refer to the database manuals:

• Oracle
• SQL Server
• MySQL
• PostgreSQL

 

How Long Can A Table Name Be in SQL?

The length of a table name depends on the database you’re using:

• Oracle (before v12.2): 30 characters
• Oracle (after v12.2): 128 characters
• SQL Server: 128 characters
• MySQL: 64 characters
• PostgreSQL: 63 characters

 

Create Table Primary Key Syntax

You can specify a primary key on a table when you create in two ways:

1. Next to the column data type when you declare it on the same line (an inline constraint)
2. At the end of all column declarations (an out of line constraint)

The method you choose would be dependent on your team’s standards and whether or not you need to have two or more columns in your primary key (you can only specify one column in an inline constraint).

To declare a primary key inline using a CREATE TABLE statement:

CREATE table_name (
  column_name data_type PRIMARY KEY,
  ...
);

That’s it. You just add the words PRIMARY KEY after the data type. They are separate words – there’s no underscore or hyphen between them.

The out of line method of declaring a primary key in a Create Table command is a bit different.

CREATE table_name (
  column_name data_type,
  ...
  CONSTRAINT pk_tbl1 PRIMARY KEY (column_name)
);

You need to add it after all of your column definitions. You also need to start with the word CONSTRAINT.

Then, give the primary key a name. You only need to do this for out of line constraints. In this example, I’ve given it the name of “pk_tbl1”, to indicate that it is a primary key, and the “tbl1” would be the name of the table.

Inside the brackets after the word PRIMARY KEY, you specify the column names. If there is more than one, separate them by a comma.

 

Create Table Foreign Key Syntax

Like the primary key definition, you can declare a foreign key both inline and out of line in the SQL Create Table command.

Inline foreign keys are declared like this:

CREATE table_name (
  column_name data_type REFERENCES other_table_name(other_column_name),
  ...
);

You use the word REFERENCES, then the name of the table that the foreign key refers to, then within brackets you specify the column that the foreign key links to.

The other method of adding a foreign key using the CREATE TABLE command is the out of line method.

CREATE table_name (
  column_name data_type,
  ...
  CONSTRAINT fk_tbl1_tbl2 FOREIGN KEY (this_tables_column)
  REFERENCES other_table_name (other_column_name)
);

You need to start with the word CONSTRAINT, then the name of the foreign key. The name needs to be unique across the database, so I like to start with the term “fk”, then the two tables I am referring to.

Then, you specify the words FOREIGN KEY, then the name of the other table within brackets. Then you add the word REFERENCES, then within brackets, you specify the column name from the table you’re referring to (which is probably the primary key).

 

Is There A CREATE TABLE IF NOT EXISTS Command?

Some databases have CREATE TABLE IF NOT EXISTS, others don’t.

• Oracle: No, but there is a workaround.
• SQL Server: No, but there is a workaround.
• MySQL: Yes, there is.
• PostgreSQL: Yes, there is

As mentioned in this StackOverflow answer:

Normally, it doesn’t make a lot of sense to check whether a table exists or not because objects shouldn’t be created at runtime and the application should know what objects were created at install time. If this is part of the installation, you should know what objects exist at any point in the process so you shouldn’t need to check whether a table already exists.

 

Oracle CREATE TABLE IF NOT EXISTS Equivalent

To check if a table exists before creating it, you’ll need to write a PL/SQL block. There are a few ways you can check:

1. You can attempt to create a table, and catch the error that appears (ORA-00955: name is already in use by an existing object).
2. You can query the USER_TABLES view to find a count where the table name matches, and check if the value is > 0.
3. You can drop the table and then create it again.

I’ve written about finding a list of tables in Oracle here.

 

SQL Server CREATE TABLE IF NOT EXISTS Equivalent

To check if a table exists before creating it, you can enclose the CREATE TABLE statement inside an IF statement.

IF NOT EXISTS (
  SELECT *
  FROM sys.tables t
  INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
  WHERE s.name = 'my_schema_name'
  AND t.name = 'table_name'
)
CREATE TABLE table_name (
  column_name data_type
);

 

MySQL CREATE TABLE IF NOT EXISTS

To create a table if it does not exist in MySQL, you simply add IF NOT EXISTS to the CREATE TABLE statement:

CREATE TABLE IF NOT EXISTS table_name (
  column_name data_type
);

 

PostgreSQL CREATE TABLE IF NOT EXISTS

In PostgreSQL 9.1, this feature exists. You can simply add IF NOT EXISTS to the CREATE TABLE statement:

CREATE TABLE IF NOT EXISTS table_name (
  column_name data_type
);

For earlier versions, one workaround is to use a function. But if you are creating a function to create a table if it doesn’t exist, perhaps there’s a better approach to your problem you can take.

 

CREATE TABLE AS SELECT (CTAS)

SQL allows you to create a table based on a SELECT statement.

This is a very useful feature of the database.

It’s great for development – if you want to copy an existing table and use it for testing or development purposes without changing the real table. Or, if you want a similar table with the same structure.

The syntax for using this command is:

CREATE TABLE table_name AS (
SELECT select_query
);

It’s also referred to as CTAS.

You specify the table_name for your new table, as well as the SELECT query to base it from.

If you want all records to be copied to the new table, you could specify SELECT * FROM old_table.

You can use a WHERE clause to restrict the values to be copied across.

Alternatively, you can create your table like normal, without the AS SELECT, and use an INSERT INTO SELECT to populate the records into the table.

 

CREATE TABLE AS SELECT Without Copying Data

You can also use the CREATE TABLE AS SELECT to copy a table’s structure without any of the data.

It’s easier than trying to generate a create table script from an existing table.

To do this, you just adjust your WHERE clause in the SELECT query to ensure there are no rows returned.

How do you guarantee this?

By adding something that is clearly evaluated to FALSE.

A common example is WHERE 1=0. Or WHERE 1=2. Really, anything involving two numbers that are not equal will work.

So, the SELECT statement will return the column names and data types, but no data. When the table is created, it will have no data in it.

The syntax for this would be:

CREATE TABLE table_name AS (
SELECT * FROM old_table WHERE 1=0
);

 

Oracle CREATE TABLE Errors and Exceptions

These are some of the errors and exceptions that may appear when you’re creating a table. As a database developer, you’ll get these kinds of errors all the time!

Exception: ORA-00955: name is already used by an existing object

Reason: You’re attempting to create a table with a name that is already being used.

Resolution: Use a different name for your table, or drop the table with the existing name. Or, use a different schema for your table if that’s applicable.

 

Exception: ORA-02260: table can have only one primary key

Reason: You’re attempting to add a primary key to a table that already has one.

Resolution: Review your SQL CREATE TABLE statement and remove all but one PRIMARY KEY definition.

 

Exception: ORA-02267: column type incompatible with referenced column type

Reason: The column you’re referring to is not compatible with the column you’re defining it on. This could happen when defining a foreign key.

Resolution: Make sure the data types are the same for both columns. And, make sure you’re not referring to the same table for the foreign key (it should be a different table).

 

Exception: ORA-00904: : invalid identifier

Reason: There are many reasons for this error, but it’s usually a syntax error.

Resolution: Check that you have all of your commas and brackets in the right places in your statement.

 

Exception: ORA-00957: duplicate column name

Reason: You have two columns with the same name.

Resolution: Rename one of your columns to make sure it is not a duplicate. Also, check that your commas and brackets are all in the right places.

 

CREATE TABLE Examples

Alright, now it’s time to look at some examples of creating a table.

These CREATE TABLE examples cover all of the topics I’ve mentioned in this article. It usually helps to see examples with data and real names, rather than syntax.

Each of the examples demonstrates the SQL using syntax for Oracle, SQL Server, MySQL, and PostgreSQL.

 

Example 1 – Basic Table

This example uses a CREATE TABLE statement that creates a simple table with a couple of columns.

Oracle

CREATE TABLE example1 (
  table_id NUMBER(10),
  first_name VARCHAR2(50)
);

SQL Server

CREATE TABLE example1 (
  table_id INT,
  first_name VARCHAR(50)
);

MySQL

CREATE TABLE example1 (
  table_id INT(10),
  first_name VARCHAR(50)
);

Postgres

CREATE TABLE example1 (
  table_id INTEGER,
  first_name VARCHAR(50)
);

 

Example 2 – Large Table

This CREATE TABLE example includes many different columns and data types.

Oracle

CREATE TABLE example2 (
    table_id NUMBER(10),
    first_name VARCHAR2(50),
    last_name VARCHAR2(200),
    registration_date DATE,
    registration_category CHAR(1),
    upload_data BLOB
  );

SQL Server

CREATE TABLE example2 (
    table_id INT,
    first_name VARCHAR(50),
    last_name VARCHAR(200),
    registration_date DATE,
    registration_category CHAR(1),
    upload_data BLOB
  );

MySQL

CREATE TABLE example2 (
    table_id INT(10),
    first_name VARCHAR(50),
    last_name VARCHAR(200),
    registration_date DATE,
    registration_category CHAR(1),
    upload_data BLOB
  );

Postgres

CREATE TABLE example2 (
    table_id INTEGER,
    first_name VARCHAR(50),
    last_name VARCHAR(200),
    registration_date DATE,
    registration_category CHAR(1),
    upload_data BLOB
  );

 

 

Example 3 – Large Table with NOT NULL and DEFAULT

This CREATE TABLE example is similar to Example 2 but includes several NOT NULL constraints and some default values.

Oracle

CREATE TABLE example3 (
    table_id NUMBER(10),
    first_name VARCHAR2(50) NOT NULL,
    last_name VARCHAR2(200) NOT NULL,
    registration_date DATE DEFAULT SYSDATE,
    registration_category CHAR(1) DEFAULT 'B'
  );

SQL Server

CREATE TABLE example3 (
    table_id INT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(200) NOT NULL,
    registration_date DATE DEFAULT GETDATE(),
    registration_category CHAR(1) DEFAULT 'B'
  );

MySQL

CREATE TABLE example3 (
    table_id INT(10),
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(200) NOT NULL,
    registration_date DATE DEFAULT CURRENT_DATE,
    registration_category CHAR(1) DEFAULT 'B'
  );

Postgres

CREATE TABLE example3 (
    table_id INTEGER,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(200) NOT NULL,
    registration_date DATE DEFAULT CURRENT_DATE,
    registration_category CHAR(1) DEFAULT 'B'
  );

 

Note: Prior to MySQL v8.0.13, using a function or expression (such as CURRENT_DATE) as a column’s default value was not supported. From the MySQL documentation:

With one exception, the default value specified in a DEFAULT clause must be a literal constant; it cannot be a function or an expression. This means, for example, that you cannot set the default for a date column to be the value of a function such as NOW() or CURRENT_DATE. The exception is that, for TIMESTAMP and DATETIME columns, you can specify CURRENT_TIMESTAMP as the default.

As of 8.0.13 this now works. You can set CURRENT_DATE as the default for a date column.

 

Example 4 – Inline Primary Key

This example of the CREATE TABLE statement uses a primary key that is defined inline.

The syntax is the same for all databases, only the data types of the columns are different.

Oracle

CREATE TABLE example4 (
    table_id NUMBER(10) PRIMARY KEY,
    first_name VARCHAR2(50)
  );

SQL Server

CREATE TABLE example4 (
    table_id INT PRIMARY KEY,
    first_name VARCHAR(50)
  );

MySQL

CREATE TABLE example4 (
    table_id INT(10) PRIMARY KEY,
    first_name VARCHAR(50)
  );

Postgres

CREATE TABLE example4 (
    table_id INTEGER PRIMARY KEY,
    first_name VARCHAR(50)
  );

 

Example 5 – Inline Primary Key and Foreign Key

This CREATE TABLE example uses an inline primary key and inline foreign key.

The syntax is the same for all databases, only the data types of the columns are different.

Oracle

CREATE TABLE example5 (
    table_id NUMBER(10) PRIMARY KEY,
    example_table_id NUMBER(10) REFERENCES example4(table_id),
    first_name VARCHAR2(50),
    last_name VARCHAR2(200)
  );

SQL Server

CREATE TABLE example5 (
    table_id INT PRIMARY KEY,
    example_table_id INT REFERENCES example4(table_id),
    first_name VARCHAR(50),
    last_name VARCHAR(200)
  );

MySQL

CREATE TABLE example5 (
    table_id INT(10) PRIMARY KEY,
    example_table_id INT(10) REFERENCES example4(table_id),
    first_name VARCHAR(50),
    last_name VARCHAR(200)
  );

Postgres

CREATE TABLE example5 (
    table_id INTEGER PRIMARY KEY,
    example_table_id INTEGER REFERENCES example4(table_id),
    first_name VARCHAR(50),
    last_name VARCHAR(200)
  );

 

Example 6 – Out of Line Primary Key and Foreign Key

This Oracle CREATE TABLE example declares a primary key and foreign key out of line (at the end of the column declarations).

The syntax is the same for all databases, only the data types of the columns are different.

Oracle

CREATE TABLE example6 (
    table_id NUMBER(10),
    example_table_id NUMBER(10),
    first_name VARCHAR2(50),
    last_name VARCHAR2(200),
    CONSTRAINT pk_ex6 PRIMARY KEY (table_id),
    CONSTRAINT fk_ex6_ex5 FOREIGN KEY (example_table_id) REFERENCES example5 (table_id)
  );

SQL Server

CREATE TABLE example6 (
    table_id INT,
    example_table_id INT,
    first_name VARCHAR(50),
    last_name VARCHAR(200),
    CONSTRAINT pk_ex6 PRIMARY KEY (table_id),
    CONSTRAINT fk_ex6_ex5 FOREIGN KEY (example_table_id) REFERENCES example5 (table_id)
  );

MySQL

CREATE TABLE example6 (
    table_id INT(10),
    example_table_id INT(10),
    first_name VARCHAR(50),
    last_name VARCHAR(200),
    CONSTRAINT pk_ex6 PRIMARY KEY (table_id),
    CONSTRAINT fk_ex6_ex5 FOREIGN KEY (example_table_id) REFERENCES example5 (table_id)
  );

Postgres

CREATE TABLE example6 (
    table_id INTEGER,
    example_table_id INTEGER,
    first_name VARCHAR(50),
    last_name VARCHAR(200),
    CONSTRAINT pk_ex6 PRIMARY KEY (table_id),
    CONSTRAINT fk_ex6_ex5 FOREIGN KEY (example_table_id) REFERENCES example5 (table_id)
  );

 

Example 7 – More Constraints

This example uses CREATE TABLE to declare a table with a primary key, foreign key, unique constraint, and check constraint.

Oracle

CREATE TABLE example7 (
    table_id NUMBER(10),
    first_name VARCHAR2(50) NOT NULL,
    last_name VARCHAR2(200) NOT NULL,
    registration_date DATE DEFAULT SYSDATE,
    registration_category CHAR(1) DEFAULT 'B',
    registration_number NUMBER(10),
    example_table_id NUMBER(10),
    CONSTRAINT pk_ex7 PRIMARY KEY (table_id),
    CONSTRAINT fk_ex7_ex5 FOREIGN KEY (example_table_id) REFERENCES example5 (table_id),
    CONSTRAINT ck_ex7_regcat CHECK (registration_category IN ('B', 'C', 'W', 'P')),
    CONSTRAINT uc_ex7_regno UNIQUE (registration_category, registration_number)
  );

SQL Server

CREATE TABLE example7 (
    table_id INT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(200) NOT NULL,
    registration_date DATE DEFAULT GETDATE(),
    registration_category CHAR(1) DEFAULT 'B',
    registration_number INT,
    example_table_id INT,
    CONSTRAINT pk_ex7 PRIMARY KEY (table_id),
    CONSTRAINT fk_ex7_ex5 FOREIGN KEY (example_table_id) REFERENCES example5 (table_id),
    CONSTRAINT ck_ex7_regcat CHECK (registration_category IN ('B', 'C', 'W', 'P')),
    CONSTRAINT uc_ex7_regno UNIQUE (registration_category, registration_number)
  );

MySQL

CREATE TABLE example7 (
    table_id INT(10),
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(200) NOT NULL,
    registration_date DATE DEFAULT CURRENT_DATE,
    registration_category CHAR(1) DEFAULT 'B',
    registration_number INT(10),
    example_table_id INT(10),
    CONSTRAINT pk_ex7 PRIMARY KEY (table_id),
    CONSTRAINT fk_ex7_ex5 FOREIGN KEY (example_table_id) REFERENCES example5 (table_id),
    CONSTRAINT ck_ex7_regcat CHECK (registration_category IN ('B', 'C', 'W', 'P')),
    CONSTRAINT uc_ex7_regno UNIQUE (registration_category, registration_number)
  );

Postgres

CREATE TABLE example7 (
    table_id INTEGER,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(200) NOT NULL,
    registration_date DATE DEFAULT CURRENT_DATE,
    registration_category CHAR(1) DEFAULT 'B',
    registration_number INTEGER,
    example_table_id INTEGER,
    CONSTRAINT pk_ex7 PRIMARY KEY (table_id),
    CONSTRAINT fk_ex7_ex5 FOREIGN KEY (example_table_id) REFERENCES example5 (table_id),
    CONSTRAINT ck_ex7_regcat CHECK (registration_category IN ('B', 'C', 'W', 'P')),
    CONSTRAINT uc_ex7_regno UNIQUE (registration_category, registration_number)
  );

 

Example 8 – Create Table as Select with All Columns

This example uses the Create Table as Select to create a table from another table, using all columns.

The syntax is the same for Oracle, SQL Server, MySQL, and PostgreSQL.

CREATE TABLE example8 AS (
  SELECT *
  FROM example7
);

Example 9 – Create Table as Select with Some Columns

This example uses the Create Table as Select to create a table from another table, using only some of the columns.

The syntax is the same for Oracle, SQL Server, MySQL, and PostgreSQL.

CREATE TABLE example9 AS (
  SELECT table_id, first_name, last_name
  FROM example7
);

Example 10 – Create Table as Select with No Data

This example uses the Create Table as Select to create a table from another table, but no data is added to the new table.

The syntax is the same for Oracle, SQL Server, MySQL, and PostgreSQL.

CREATE TABLE example10 AS (
  SELECT table_id, first_name, last_name
  FROM example7
  WHERE 1=0
);

 

Summary of Differences Between Vendors

This table shows a summary of differences in the CREATE TABLE statement between different databases.

Criteria	Oracle	SQL Server	MySQL	PostgreSQL
Max Table Name Length	128	128	64	63
Create Table If Not Exists?	No, but there is a workaround	No, but there is a workaround	Yes	Yes
Default Date to Function	Yes	Yes	Yes, from 8.0.13	Yes

 

Conclusion

So, there’s your guide to the SQL CREATE TABLE statement. If you have any questions on any of the parts of a CREATE TABLE statement, let me know in the comments below.