SQL CREATE TABLE Statement

Learn how to use SQL CREATE TABLE to define tables, columns, data types, sizes, prices, schemas, and optional constraints.

CREATE TABLE is a SQL Data Definition Language (DDL) statement. It defines and creates a new table, including the table name, columns, data types, and optional constraints. The statement creates the table structure; it does not add data rows. Rows are added later with an INSERT statement.

What CREATE TABLE Does

A table is a database object that stores related records in rows organized by columns. A column is a named field that stores one category of data, such as a product name or price. A data type specifies what kind of values a column can store.

When the database executes CREATE TABLE, it:

• Creates a new, initially empty table.
• Records the table's schema, meaning its structural definition, in the database.
• Defines the names and data types of its columns.
• May apply rules such as primary keys, required values, or permitted relationships.

Creating a table and inserting rows are separate operations. For example, CREATE TABLE defines a Products table, while INSERT later adds individual products to it.

Basic CREATE TABLE Syntax

CREATE TABLE table_name (
  column_name1 data_type(size),
  column_name2 data_type(size),
  column_name3 data_type(precision, scale)
);

The statement begins with CREATE TABLE, followed by the table name. A parenthesized list contains the column definitions. Each definition normally includes a column name and a data type. Definitions are separated by commas, but the final definition normally must not have a comma before the closing parenthesis.

Defining Columns

Every column definition needs a name and a data type. Choose meaningful names such as product_name and product_price. Column names must also follow the naming rules of the selected database management system (DBMS). Reserved words, spaces, capitalization, and quoting rules differ between systems.

Some data types accept parameters. Character types commonly accept a length. Exact numeric types commonly accept precision and scale.

Character Types

CHAR(length) is a fixed-length character type. It is useful when values have a consistent size, such as fixed-format identifiers. For example, CHAR(15) declares a character field with a length of 15 according to the target DBMS's rules.

VARCHAR(length) is a variable-length character type supported by many SQL dialects. It is often more suitable for names and descriptions whose lengths vary. The exact type name, maximum length, and storage behavior vary by DBMS.

Do not choose text lengths arbitrarily. Estimate the largest valid value the application needs, while allowing reasonable future values. A length that is too small can reject valid input; one that is unnecessarily large can make validation and data design less precise.

Exact Numeric Values

DECIMAL(precision, scale) stores exact numeric values. It is commonly used for prices and other currency amounts because exact decimal arithmetic avoids many floating-point representation issues.

• Precision is the total number of digits allowed.
• Scale is the number of digits allowed to the right of the decimal point.

For example, DECIMAL(8, 2) allows eight total digits, including two digits after the decimal point. That leaves up to six digits before the decimal point. Select precision and scale from the expected range and required accuracy of the data.

Worked Example: Products Table

CREATE TABLE Products (
  product_id CHAR(15),
  vendor_id CHAR(15),
  product_name CHAR(254),
  product_price DECIMAL(8, 2)
);

This statement creates an empty table named Products.

The comma after product_id, vendor_id, and product_name separates those definitions from the next one. There is no comma after the final product_price definition.

Adding a Primary Key

A constraint is a rule that restricts permitted values or relationships in a table. Common constraints include PRIMARY KEY, FOREIGN KEY, NOT NULL, UNIQUE, CHECK, and DEFAULT.

A product identifier is a likely candidate for a primary key. A primary key is a column or set of columns that uniquely identifies each row.

CREATE TABLE Products (
  product_id CHAR(15) PRIMARY KEY,
  vendor_id CHAR(15),
  product_name CHAR(254),
  product_price DECIMAL(8, 2)
);

Here, product_id is declared as the primary key while the other columns retain their basic definitions. Constraints can also be written as separate table-level definitions, depending on the design and the DBMS.

Schema-Qualified Tables

A schema is a namespace that groups database objects such as tables. In systems that support schemas, a table may be created with a qualified name:

CREATE TABLE sales.Products (
  product_id CHAR(15),
  vendor_id CHAR(15),
  product_name VARCHAR(254),
  product_price DECIMAL(8, 2)
);

The form schema_name.table_name requests the table in a particular schema. Whether this syntax, the schema name, and the required privileges are available depends on the DBMS.

DBMS Differences

SQL dialects differ. Before running a statement, check the documentation for the target database platform.

Some systems support conditional creation:

CREATE TABLE IF NOT EXISTS Products (
  product_id CHAR(15),
  product_price DECIMAL(8, 2)
);

This syntax is dialect dependent. Do not assume it works on every SQL platform.

Safe and Valid Table Creation

• A table name must be unique within the relevant schema unless a dialect-specific conditional clause handles an existing table.
• Column names within one table must not be duplicated.
• Data type names and parameters must be valid for the target DBMS.
• Creating a table changes database structure and may require CREATE privileges in the database or schema.
• Use a schema-qualified name when the application requires a particular schema and the DBMS supports it.

Inspecting the Created Table

After executing CREATE TABLE, inspect the resulting schema with a command or metadata tool supported by your platform.

-- MySQL or MariaDB
DESCRIBE Products;

-- PostgreSQL psql client
\d Products;

-- SQL Server
EXEC sp_help 'Products';

These commands are examples rather than universal SQL syntax. They show the general purpose of inspection: verify the table name, columns, data types, lengths, precision, scale, and constraints.

Troubleshooting CREATE TABLE Errors

Syntax error near the closing parenthesis

The most common cause is a comma after the final column definition:

CREATE TABLE Products (
  product_id CHAR(15),
);

Remove the final comma before the closing parenthesis.

Table already exists

The name is already in use in the current schema. Choose another name, alter or remove the existing object only when appropriate, or use CREATE TABLE IF NOT EXISTS if the target DBMS supports it.

Unknown or invalid data type

The type name or its parameters may not exist in the selected SQL dialect. Check the DBMS documentation and use its supported equivalent and parameter format.

Permission denied

The database user may lack permission to create objects in the selected database or schema. Use an authorized schema or ask a database administrator for the required privilege.

Price values are rounded unexpectedly

A floating-point type may have been selected for currency, or the DECIMAL precision and scale may be too small. Use an exact decimal type with suitable parameters, such as DECIMAL(8, 2) for values requiring up to six digits before the decimal point and two after it.

Next Steps

CREATE TABLE defines structure only. Use INSERT to add rows after the table exists. Logical follow-up topics include the SQL CREATE DATABASE statement, SQL constraints, primary and foreign keys, data types, ALTER TABLE, DROP TABLE, schemas, and permissions.