Join us on
Star us on
Get Started
Slack
GitHub
Get Started
v2.0 (latest version) v1.3 (earlier version) v1.2 (earlier version) v1.1 (earlier version) v1.0 (earlier version)
  • GET STARTED
    • Quick start
      • 1. Install YugabyteDB
      • 2. Create a local cluster
      • 3. Explore YSQL
      • 4. Build an application
        • Java
        • NodeJS
        • Go
        • Python
        • Ruby
        • C#
        • PHP
        • C++
        • C
    • Introduction
    • Explore core
      • Linear scalability
      • Fault tolerance
      • Global distribution
      • Auto-sharding
      • Tunable reads
      • Observability
      • Two data center deployment
  • USER GUIDES
    • Develop
      • Learn app development
        • 1. SQL vs NoSQL
        • 2. Data modeling
        • 3. Data types
        • 4. ACID transactions
        • 5. Aggregations
        • 6. Batch operations
        • 7. Date and time
        • 8. Strings and text
      • Ecosystem integrations
        • Apache Kafka
        • Apache Spark
        • JanusGraph
        • KairosDB
        • Presto
        • Metabase
      • Real-world examples
        • E-Commerce App
        • IoT Fleet Management
        • Retail Analytics
      • Work with GraphQL
        • Hasura
        • Prisma
      • Explore sample applications
    • Deploy
      • Deployment checklist
      • Manual deployment
        • 1. System configuration
        • 2. Install software
        • 3. Start YB-Masters
        • 4. Start YB-TServers
        • 5. Verify deployment
      • Kubernetes
        • Helm chart
        • Helm configuration
        • Local SSD
        • Rook operator
        • YugabyteDB operator
      • Docker
      • Public clouds
        • Amazon Web Services
        • Google Cloud Platform
        • Microsoft Azure
      • Change data capture (CDC)
        • CDC to Kafka
        • CDC to stdout
      • Two data center (2DC)
      • Pivotal Cloud Foundry
      • Yugabyte Platform
        • 1. Prepare cloud environment
        • 2. Install Admin Console
        • 3. Configure Admin Console
        • 4. Configure cloud providers
    • Benchmark
      • Performance
      • YCSB
      • Large datasets
    • Secure
      • Security checklist
      • Authentication
        • Authentication
        • Fine-grained authentication
      • Authorization
        • RBAC model
        • Create roles
        • Grant privileges
      • Encryption in transit
        • 1. Prepare nodes
        • 2. Server-server encryption
        • 3. Client-server encryption
        • 4. Connect to cluster
      • Encryption at rest
    • Manage
      • Backup and restore
        • Backing up data
        • Restoring data
        • Backing Up Data using Snapshots
      • Data migration
        • Bulk import
        • Bulk export
      • Change cluster configuration
      • Diagnostics reporting
      • Upgrade deployment
      • Yugabyte Platform
        • Create universe - Multi-zone
        • Create universe - Multi-region
        • Edit universe
        • Edit configuration options
        • Health checking and alerts
        • Create and edit instance tags
        • Node status and actions
        • Read replicas
        • Back up and restore
        • Upgrade universe
        • Delete universe
    • Troubleshoot
      • Troubleshooting
      • Cluster level issues
        • YCQL connection issues
        • YEDIS connection Issues
        • Recover tserver/master
      • Node level issues
        • Check processes
        • Inspect logs
        • System statistics
      • Yugabyte Platform
        • Troubleshoot universes
  • REFERENCE
    • Configuration
      • YB-TServer service
      • YB-Master service
      • Default ports
    • APIs
      • YSQL
        • Statements
          • ABORT
          • ALTER DATABASE
          • ALTER DEFAULT PRIVILEGES
          • ALTER DOMAIN
          • ALTER GROUP
          • ALTER POLICY
          • ALTER ROLE
          • ALTER SEQUENCE
          • ALTER TABLE
          • ALTER USER
          • BEGIN
          • COMMIT
          • COPY
          • CREATE AGGREGATE
          • CREATE CAST
          • CREATE DATABASE
          • CREATE DOMAIN
          • CREATE FUNCTION
          • CREATE GROUP
          • CREATE INDEX
          • CREATE OPERATOR
          • CREATE OPERATOR CLASS
          • CREATE POLICY
          • CREATE PROCEDURE
          • CREATE ROLE
          • CREATE RULE
          • CREATE SCHEMA
          • CREATE SEQUENCE
          • CREATE TABLE
          • CREATE TABLE AS
          • CREATE TRIGGER
          • CREATE TYPE
          • CREATE USER
          • CREATE VIEW
          • DEALLOCATE
          • DELETE
          • DO
          • DROP AGGREGATE
          • DROP CAST
          • DROP DATABASE
          • DROP DOMAIN
          • DROP FUNCTION
          • DROP GROUP
          • DROP OPERATOR
          • DROP OPERATOR CLASS
          • DROP OWNED
          • DROP POLICY
          • DROP PROCEDURE
          • DROP ROLE
          • DROP RULE
          • DROP SEQUENCE
          • DROP TABLE
          • DROP TRIGGER
          • DROP TYPE
          • DROP USER
          • END
          • EXECUTE
          • EXPLAIN
          • GRANT
          • INSERT
          • LOCK
          • PREPARE
          • REASSIGN OWNED
          • RESET
          • REVOKE
          • ROLLBACK
          • SELECT
          • SET
          • SET CONSTRAINTS
          • SET ROLE
          • SET SESSION AUTHORIZATION
          • SET TRANSACTION
          • SHOW
          • SHOW TRANSACTION
          • TRUNCATE
          • UPDATE
        • Data types
          • Binary
          • Boolean
          • Character
          • Date-time
          • Json
          • Money
          • Numeric
          • Serial
          • UUID
        • Expressions
          • currval()
          • lastval()
          • nextval()
        • Extensions
        • Keywords
        • Reserved names
      • YCQL
        • Quick start YCQL
        • ALTER KEYSPACE
        • ALTER ROLE
        • ALTER TABLE
        • CREATE INDEX
        • CREATE KEYSPACE
        • CREATE ROLE
        • CREATE TABLE
        • CREATE TYPE
        • DROP INDEX
        • DROP KEYSPACE
        • DROP ROLE
        • DROP TABLE
        • DROP TYPE
        • GRANT PERMISSION
        • GRANT ROLE
        • REVOKE PERMISSION
        • REVOKE ROLE
        • USE
        • INSERT
        • SELECT
        • UPDATE
        • DELETE
        • TRANSACTION
        • TRUNCATE
        • Simple Value
        • Subscript
        • Function Call
        • Operator Call
        • BLOB
        • BOOLEAN
        • MAP, SET, LIST
        • FROZEN
        • INET
        • Integer & Counter
        • Non-Integer
        • TEXT
        • Date & Time Types
        • UUID & TIMEUUID
        • JSONB
        • Date and time Functions
    • CLIs
      • yb-ctl
      • yb-docker-ctl
      • ysqlsh
      • cqlsh
      • yb-admin
    • Sample data
      • Chinook
      • Northwind
      • PgExercises
      • SportsDB
    • Tools
      • DBeaver
      • pgAdmin
      • SQL Workbench/J
      • TablePlus
      • Visual Studio Code
    • Connectors and drivers
      • PostgreSQL JDBC Driver
      • YugabyteDB JDBC Driver
      • Kafka Connect YugabyteDB
      • Spring Data YugabyteDB
  • RELEASES
    • Release history
      • v2.0.7
      • v2.0.6
      • v2.0.5
      • v2.0.3
      • v2.0.1
      • v2.0.0
      • v1.3.1
      • v1.3.0
      • v1.2.12
      • v1.2.11
      • v1.2.10
      • v1.2.9
      • v1.2.8
      • v1.2.6
      • v1.2.5
      • v1.2.4
  • CONCEPTS
    • Architecture
      • Design goals
      • Key concepts
        • Universe
        • YB-TServer
        • YB-Master
        • Acknowledgements
      • Core functions
        • Universe creation
        • Table creation
        • Write IO path
        • Read IO path
        • High availability
      • Layered architecture
      • Query layer
        • Overview
      • DocDB store
        • Sharding
        • Replication
        • Persistence
        • Performance
      • DocDB transactions
        • Isolation Levels
        • Single row transactions
        • Distributed transactions
        • Transactional IO path
      • Change data capture (CDC)
      • Two data center (2DC) deployments
  • FAQ
    • Comparisons
      • CockroachDB
      • Google Cloud Spanner
      • MongoDB
      • FoundationDB
      • Amazon DynamoDB
      • Azure Cosmos DB
      • Apache Cassandra
      • Redis in-memory store
      • Apache HBase
    • Other FAQs
      • Product
      • Architecture
      • Yugabyte Platform
      • API compatibility
  • CONTRIBUTE
    • Get involved
    • Core database
      • Checklist
      • Building the source
      • Running the Tests
  • Misc
    • YEDIS
      • Quick start
      • Develop
        • Client drivers
          • C
          • C++
          • C#
          • Go
          • Java
          • NodeJS
          • Python
      • API reference
        • APPEND
        • AUTH
        • CONFIG
        • CREATEDB
        • DELETEDB
        • LISTDB
        • SELECT
        • DEL
        • ECHO
        • EXISTS
        • EXPIRE
        • EXPIREAT
        • FLUSHALL
        • FLUSHDB
        • GET
        • GETRANGE
        • GETSET
        • HDEL
        • HEXISTS
        • HGET
        • HGETALL
        • HINCRBY
        • HKEYS
        • HLEN
        • HMGET
        • HMSET
        • HSET
        • HSTRLEN
        • HVALS
        • INCR
        • INCRBY
        • KEYS
        • MONITOR
        • PEXPIRE
        • PEXPIREAT
        • PTTL
        • ROLE
        • SADD
        • SCARD
        • RENAME
        • SET
        • SETEX
        • PSETEX
        • SETRANGE
        • SISMEMBER
        • SMEMBERS
        • SREM
        • STRLEN
        • ZRANGE
        • TSADD
        • TSCARD
        • TSGET
        • TSLASTN
        • TSRANGEBYTIME
        • TSREM
        • TSREVRANGEBYTIME
        • TTL
        • ZADD
        • ZCARD
        • ZRANGEBYSCORE
        • ZREM
        • ZREVRANGE
        • ZSCORE
        • PUBSUB
        • PUBLISH
        • SUBSCRIBE
        • UNSUBSCRIBE
        • PSUBSCRIBE
        • PUNSUBSCRIBE
> APIs > YSQL > Statements >

CREATE TABLE


    • Synopsis
    • Syntax
    • Semantics
      • Primary key
      • Foreign key
      • Unique
      • Check
      • Default
      • Temporary or Temp
      • Split Into
      • Storage parameters
    • Examples
      • Table with primary key
      • Table with range primary key
      • Table with check constraint
      • Table with default value
      • Table with foreign key constraint
      • Table with unique constraint
      • Create table specifying number of tablets
    • See also

Synopsis

Use the CREATE TABLE statement to create a new table in a database. It defines the table name, column names and types, primary key, and table properties.

Syntax

  • Grammar
  • Diagram
create_table ::= CREATE [ TEMPORARY | TEMP ] TABLE [ IF NOT EXISTS ] 
                 table_name ( [ table_elem [ , ... ] ] ) 
                 [ WITH ( storage_parameters ) | WITHOUT OIDS ] 
                 [ SPLIT ( INTO integer TABLETS ) ]

table_elem ::= column_name data_type [ column_constraint [ ... ] ]
               | table_constraint

column_constraint ::= [ CONSTRAINT constraint_name ] 
                      { NOT NULL
                        | NULL
                        | CHECK ( expression )
                        | DEFAULT expression
                        | UNIQUE index_parameters
                        | PRIMARY KEY
                        | references_clause }

table_constraint ::= [ CONSTRAINT constraint_name ] 
                     { CHECK ( expression )
                       | UNIQUE ( column_names ) index_parameters
                       | PRIMARY KEY ( key_columns )
                       | FOREIGN KEY ( column_names ) 
                         references_clause }

key_columns ::= hash_columns [ , range_columns ] | range_columns

hash_columns ::= column_name [ HASH ] | ( column_name [ , ... ] ) HASH

range_columns ::= { column_name { ASC | DESC } } [ , ... ]

storage_parameters ::= storage_parameter [ , ... ]

storage_parameter ::= param_name [ = param_value ]

index_parameters ::= [ INCLUDE ( column_names ) ] 
                     [ WITH ( storage_parameters ) ]

references_clause ::= REFERENCES table_name [ column_name [ , ... ] ] 
                      [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]  
                      [ ON DELETE key_action ] 
                      [ ON UPDATE key_action ]

create_table

CREATETEMPORARYTEMPTABLEIFNOTEXISTStable_name(,table_elem)WITH(storage_parameters)WITHOUTOIDSSPLIT(INTOintegerTABLETS)

table_elem

column_namedata_typecolumn_constrainttable_constraint

column_constraint

CONSTRAINTconstraint_nameNOTNULLNULLCHECK(expression)DEFAULTexpressionUNIQUEindex_parametersPRIMARYKEYreferences_clause

table_constraint

CONSTRAINTconstraint_nameCHECK(expression)UNIQUE(column_names)index_parametersPRIMARYKEY(key_columns)FOREIGNKEY(column_names)references_clause

key_columns

hash_columns,range_columnsrange_columns

hash_columns

column_nameHASH(,column_name)HASH

range_columns

,column_nameASCDESC

storage_parameters

,storage_parameter

storage_parameter

param_name=param_value

index_parameters

INCLUDE(column_names)WITH(storage_parameters)

references_clause

REFERENCEStable_name,column_nameMATCHFULLMATCHPARTIALMATCHSIMPLEONDELETEkey_actionONUPDATEkey_action

Semantics

Create a table with table_name. If qualified_name already exists in the specified database, an error will be raised unless the IF NOT EXISTS clause is used.

Primary key

Primary key can be defined in either column_constraint or table_constraint, but not in both. There are two types of primary key columns:

  • Hash primary key columns: The primary key may have zero or more leading hash-partitioned columns. By default, only the first column is treated as the hash-partition column. But this behavior can be modified by explicit use of the HASH annotation.

  • Range primary key columns: A table can have zero or more range primary key columns and it controls the top-level ordering of rows within a table (if there are no hash partition columns) or the ordering of rows among rows that share a common set of hash partitioned column values. By default, the range primary key columns are stored in ascending order. But this behavior can be controlled by explicit use of ASC or DESC.

For example, if the primary key specification is PRIMARY KEY ((a, b) HASH, c DESC) then columns a & b are used together to hash partition the table, and rows that share the same values for a and b are stored in descending order of their value for c.

If the primary key specification is PRIMARY KEY(a, b), then column a is used to hash partition the table and rows that share the same value for a are stored in ascending order of their value for b.

Foreign key

FOREIGN KEY and REFERENCES specifies that the set of columns can only contain values that are present in the referenced column(s) of the referenced table. It is used to enforce referential integrity of data.

Unique

This enforces that the set of columns specified in the UNIQUE constraint are unique in the table, that is, no two rows can have the same values for the set of columns specified in the UNIQUE constraint.

Check

This is used to enforce that data in the specified table meets the requirements specified in the CHECK clause.

Default

This clause is used to specify a default value for the column. If an INSERT statement does not specify a value for the column, then the default value is used. If no default is specified for a column, then the default is NULL.

Temporary or Temp

Using this qualifier will create a temporary table. Temporary tables are only visible in the current client session or transaction in which they are created and are automatically dropped at the end of the session or transaction. Any indexes created on temporary tables are temporary as well.

Split Into

The SPLIT INTO clause specifies the number of tablets that will be created for the table. This is useful for two data center (2DC) deployments. See example below: Create CDC table specifying number of tablets.

Storage parameters

Storage parameters as defined by PostgreSQL are ignored and only present for compatibility with PostgreSQL.

Examples

Table with primary key

yugabyte=# CREATE TABLE sample(k1 int,
                               k2 int,
                               v1 int,
                               v2 text,
                               PRIMARY KEY (k1, k2));

In this example, the first column k1 will be HASH, while second column k2 will be ASC.

yugabyte=# \d sample
               Table "public.sample"
 Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 k1     | integer |           | not null |
 k2     | integer |           | not null |
 v1     | integer |           |          |
 v2     | text    |           |          |
Indexes:
    "sample_pkey" PRIMARY KEY, lsm (k1 HASH, k2)

Table with range primary key

yugabyte=# CREATE TABLE range(k1 int,
                              k2 int,
                              v1 int,
                              v2 text,
                              PRIMARY KEY (k1 ASC, k2 DESC));

Table with check constraint

yugabyte=# CREATE TABLE student_grade(student_id int,
                                      class_id int,
                                      term_id int,
                                      grade int CHECK (grade >= 0 AND grade <= 10),
                                      PRIMARY KEY (student_id, class_id, term_id));

Table with default value

yugabyte=# CREATE TABLE cars(id int PRIMARY KEY,
                             brand text CHECK (brand in ('X', 'Y', 'Z')),
                             model text NOT NULL,
                             color text NOT NULL DEFAULT 'WHITE' CHECK (color in ('RED', 'WHITE', 'BLUE')));

Table with foreign key constraint

Define two tables with a foreign keys constraint.

yugabyte=# CREATE TABLE products(id int PRIMARY KEY,
                                 descr text);
yugabyte=# CREATE TABLE orders(id int PRIMARY KEY,
                               pid int REFERENCES products(id) ON DELETE CASCADE,
                               amount int);

Insert some rows.

yugabyte=# SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL SERIALIZABLE;
yugabyte=# INSERT INTO products VALUES (1, 'Phone X'), (2, 'Tablet Z');
yugabyte=# INSERT INTO orders VALUES (1, 1, 3), (2, 1, 3), (3, 2, 2);

yugabyte=# SELECT o.id AS order_id, p.id as product_id, p.descr, o.amount FROM products p, orders o WHERE o.pid = p.id;
order_id | product_id |  descr   | amount
----------+------------+----------+--------
        1 |          1 | Phone X  |      3
        2 |          1 | Phone X  |      3
        3 |          2 | Tablet Z |      2
(3 rows)

Inserting a row referencing a non-existent product is not allowed.

yugabyte=# INSERT INTO orders VALUES (1, 3, 3);
ERROR:  insert or update on table "orders" violates foreign key constraint "orders_pid_fkey"
DETAIL:  Key (pid)=(3) is not present in table "products".

Deleting a product will cascade to all orders (as defined in the CREATE TABLE statement above).

yugabyte=# DELETE from products where id = 1;
yugabyte=# SELECT o.id AS order_id, p.id as product_id, p.descr, o.amount FROM products p, orders o WHERE o.pid = p.id;
 order_id | product_id |  descr   | amount
----------+------------+----------+--------
        3 |          2 | Tablet Z |      2
(1 row)

Table with unique constraint

yugabyte=# CREATE TABLE translations(message_id int UNIQUE,
                                     message_txt text);

Create table specifying number of tablets

You can use the CREATE TABLE statement with the SPLIT INTO clause to specify the number of tablets for the table. This is useful for two data center (2DC) deployments that require identical number of tablets on both clusters.

yugabyte=# CREATE TABLE tracking (id int PRIMARY KEY) SPLIT (INTO 10 TABLETS);

See also

  • ALTER TABLE
  • CREATE TABLE AS
  • CREATE TABLESPACE
  • DROP TABLE
    • Synopsis
    • Syntax
    • Semantics
      • Primary key
      • Foreign key
      • Unique
      • Check
      • Default
      • Temporary or Temp
      • Split Into
      • Storage parameters
    • Examples
      • Table with primary key
      • Table with range primary key
      • Table with check constraint
      • Table with default value
      • Table with foreign key constraint
      • Table with unique constraint
      • Create table specifying number of tablets
    • See also
APIs
CREATE SEQUENCE
APIs
CREATE TABLE AS
Talk to Community
  • Slack
  • Github
  • Forum
  • StackOverflow
Yugabyte
Contact Us
Copyright © 2017-2019 Yugabyte, Inc. All rights reserved.