SQL CREATE TABLE Statement

Learn how to use SQL CREATE TABLE to define columns, choose data types, set lengths and precision, and add constraints.

CREATE TABLE is a SQL DDL command. DDL means Data Definition Language: SQL commands used to define or modify database structures. CREATE TABLE creates a new table definition in the current database or schema.

A table is a relational structure that stores rows organized by named columns. Creating a table defines its structure; it does not insert any data rows. Use INSERT INTO later to add rows.

Basic CREATE TABLE Syntax

CREATE TABLE [schema_name.]table_name (
  column_name data_type [column_constraint],
  column_name data_type [column_constraint],
  [table_constraint]
);

The CREATE TABLE keyword is followed by a table identifier. Column definitions go inside parentheses and are separated by commas. Each definition normally contains a column name followed by its data type and may include constraints.

The final column or table constraint normally must not have a trailing comma before the closing parenthesis. A semicolon is a common statement terminator, but whether it is required depends on the SQL client and database dialect.

Minimal Table Definition

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

Here, each item inside the parentheses is a column definition. The table has four columns: two fixed-length character columns, another character column, and an exact decimal number column. There is no comma after the last column.

Choosing a Table Name

Choose a meaningful name that describes the entity represented by the rows. For example, products is clearer than data1. Follow one consistent naming style across your database, such as lowercase names with underscores or another convention supported by your team.

Identifier rules vary by DBMS. Permitted characters, case behavior, reserved words, and quoted identifiers may differ between PostgreSQL, MySQL, SQL Server, Oracle Database, SQLite, and other systems. Avoid reserved words and unusual characters unless you understand the database-specific quoting rules.

A table can optionally be qualified with a schema name:

CREATE TABLE inventory.products (
  product_id CHAR(15),
  product_name VARCHAR(254)
);

A schema is a namespace that organizes database objects such as tables. In this example, inventory is the schema and products is the table.

Defining Columns

A column is a named field that represents one attribute of the stored entity. Every column needs a name and a data type. A data type determines the kind of value that the column can hold, such as text, an integer, a date, or a decimal number.

Use descriptive, consistent names. Singular names such as product_name, price, and created_date are common. Teams often use consistent suffixes for identifiers, dates, and status values, such as _id, _date, and _status.

Common Column Data Types

Type | Typical use | Parameter meaning | Portability notes

CHAR(n) | Fixed-width text such as a code with a known size | n is the character length | Widely available, but padding and length semantics can vary

VARCHAR(n) | Variable-width text such as names or descriptions | n is the maximum character length | Supported by many systems; exact limits and syntax vary

INTEGER | Whole numbers such as counts or identifiers | Usually no length parameter | Range and aliases vary by DBMS

DECIMAL(p,s) or NUMERIC(p,s) | Exact numbers such as prices and quantities | p is total precision; s is digits after the decimal point | Supported widely, but defaults and maximums vary

DATE | Calendar dates | Usually no length parameter | Date range and related types vary

BOOLEAN | True/false values | Usually no length parameter | Some systems use a different type or representation

CHAR is fixed-width: values are stored with a declared width, subject to the DBMS's padding behavior. VARCHAR is variable-width and is usually a better choice for text whose lengths differ. Some systems support VARCHAR2 instead of, or in addition to, VARCHAR; check the selected DBMS.

Length, Precision, and Scale

Character types can use a length parameter. For example, VARCHAR(100) declares room for up to 100 characters according to the database's length rules.

Exact numeric types use precision and scale. In DECIMAL(8,2), precision is 8, meaning eight total digits, and scale is 2, meaning two digits may appear to the right of the decimal point. Values such as 123456.78 fit because they contain eight digits total. A value that requires more than eight total digits does not fit.

Supported type names, defaults, character-length semantics, numeric limits, and type parameters vary by DBMS. Always check the syntax for the database you are using.

Adding Basic Constraints

A constraint is a rule enforced by the database to protect data integrity. Constraints can be written inline beside a column or at table level after the column definitions.

NOT NULL

NOT NULL requires a value for a column. Without it, a column generally permits the special NULL value, which represents missing or unknown data.

PRIMARY KEY

A primary key is a column or combination of columns that uniquely identifies every row. A primary key cannot contain duplicate identifying values, and its columns cannot be null.

UNIQUE

UNIQUE prevents duplicate values in a column or column combination. It is useful for an alternate identifier, such as an email address, when that value must be different for every row.

FOREIGN KEY

A foreign key defines a relationship to another table. It requires values in the child table to match an appropriate key in the referenced parent table. The referenced table and key normally need to exist first.

CHECK

CHECK restricts values using a condition where supported. For example, a product price can be required to be zero or greater.

Table with a Primary Key and Required Columns

CREATE TABLE products (
  product_id CHAR(15) PRIMARY KEY,
  vendor_id CHAR(15) NOT NULL,
  product_name VARCHAR(254) NOT NULL,
  product_price DECIMAL(8,2) NOT NULL
);

The inline primary key makes each product_id unique. Each NOT NULL column must receive a value when a row is inserted. Check whether your DBMS supports VARCHAR and whether the declared length has the behavior you expect.

Table-Level Constraints and Relationships

Table-level constraints are useful when a rule involves multiple columns, when you want to name a constraint, or when you want the relationship definition separated from an individual column.

CREATE TABLE inventory.products (
  product_id CHAR(15) PRIMARY KEY,
  vendor_id CHAR(15) NOT NULL,
  product_name VARCHAR(254) NOT NULL,
  product_price DECIMAL(8,2) CHECK (product_price >= 0),
  CONSTRAINT fk_products_vendor
    FOREIGN KEY (vendor_id) REFERENCES inventory.vendors(vendor_id)
);

This example qualifies the table with the inventory schema, rejects negative prices, and creates a named foreign key. The referenced inventory.vendors table and its compatible unique key must already exist.

CREATE TABLE Elements

Element | Required or optional | Purpose | Example

Table name | Required | Identifies the new table | products

Column name | Required for each column | Names an attribute | product_name

Data type | Required for each column | Specifies the kind of value accepted | VARCHAR(254)

Length, precision, or scale | Optional and type-dependent | Limits text size or numeric representation | DECIMAL(8,2)

Constraint | Optional | Enforces a data-integrity rule | NOT NULL or PRIMARY KEY

Schema name | Optional | Qualifies the namespace containing the table | inventory.products

DBMS-specific storage option | Optional or required in some systems | Controls physical storage or table behavior | An engine, tablespace, filegroup, or partition option

Vendor-Specific Storage Options

Some database systems allow or require physical table options. Examples include a storage engine, tablespace, filegroup, partitioning configuration, table space location, or similar setting. These options affect where or how the table is stored.

Such options are vendor-specific and are not portable SQL. Keep them separate from the portable table definition unless your application intentionally targets one DBMS.

Inspecting the Created Table

A successful CREATE TABLE creates the structure but no data rows. Inspect the result using a command supplied by your DBMS or a catalog query.

PostgreSQL

\d products

MySQL

DESCRIBE products;

Information Schema Pattern

SELECT column_name, data_type
FROM information_schema.columns
WHERE table_name = 'products';

The information_schema query is a useful metadata pattern, but available columns, filtering behavior, and privileges vary. If multiple schemas contain a table with the same name, also filter by the appropriate schema or catalog column supported by your DBMS.

What Comes After CREATE TABLE?

Use INSERT INTO to add rows.
Use ALTER TABLE to change columns or constraints after creation.
Use SQL constraints to study data-integrity rules in more detail.
Use DROP to remove a table, but only after confirming that the table and its data are no longer needed.

Troubleshooting CREATE TABLE

Syntax Error Near the Closing Parenthesis

The usual cause is a comma after the last column definition. Remove the final comma before ).

Table Already Exists

The table name is already present in the selected schema. Inspect the existing object, choose another name, or use a conditional creation form supported by your DBMS. Do not remove the old table unless doing so is safe and intentional.

Unknown or Unsupported Data Type

The selected DBMS may not support the type name or its parameters. Consult the DBMS documentation and choose an equivalent supported type. For example, VARCHAR2, boolean types, integer aliases, and automatic-numbering types differ across systems.

Numeric Definition Rejects Expected Values

Precision or scale may be too restrictive. Select precision based on the largest valid value and scale based on the required fractional digits. For example, a value larger than the range allowed by DECIMAL(8,2) requires a larger precision.

Foreign Key Creation Fails

Check that the referenced table exists, the referenced column is a primary or suitable unique key, and the related columns have compatible definitions. Create the parent table first when necessary.

Permission Denied

The current user may lack permission to create tables in the database or schema. Use an authorized schema or request the required privileges from a database administrator.