Skip to content
Home » Cloudflare D1 SQL Docs » Create Table in D1 SQL

Create Table in D1 SQL

The CREATE TABLE and DROP TABLE are essential DDL (Data definition language) statements for every database and this is true for the Cloudflare D1 database as well. In this page, we will examine how are they applied and will also mention some limitations of D1.

D1 data types

Each relational database table consists of columns of certain data type. Cloudflare D1 supports the following data types:

REALfor storing floating point numbers, 8-byte IEEE floating point number
INTEGERfor storing whole numbers up to 253-1 (= 9007199254740991)
TEXTstoring arbitrary text (up to 1 GB of text)
BLOBbinary data type (up to 2 GB)
Cloudflare D1 data types

There is also a support for a not very well documented DATETIME data type, which can store dates and can have a default value of CURRENT_TIMESTAMP, but we can also save dates in the INTEGER, TEXT and REAL data types by using the date conversion functions.

CREATE TABLE statement

The CREATE TABLE statement in D1 is used for creating a new database table. The minimum attributes that we must supply for the command are: table name, list of column names with their data types, and optionally the constraints PRIMARY KEY, UNIQUE, NOT NULL, CHECK, FOREIGN KEY.

Note: below <…> – denotes required value/name, […] – is for optional attributes, { 1 | 2 | 3 } – lists possible selection

base syntax is as follows:

CRETE TABLE <table name> [IF NOT EXISTS]
(
 <column name 1> <data type> [<constrains>] ,
 <column name 2> <data type> [<constrains>] ,
);

Example with PRIMARY KEY

This example creates table chats with PRIMARY KEY which is AUTOINCREMENT (NULL values for the chat_id column upon INSERT will receive auto value being the number of rows + 1)

CREATE TABLE chats 
(
 chat_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, 
 message TEXT, 
 created_at DATETIME
);

without PRIMARY KEY

of course there may be times where we don’t want to have a primary key, and if this is the case we can just modify the line:
chat_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL
to
chat_id INTEGER NOT NULL

PRIMARY KEY over multiple columns

We can have a primary key over multiple columns (composite key), in which case it should be specified with a separate declaration. In the SQL code below the primary key consists of the columns site_id and chat_id:

CREATE TABLE chats 
(
 site_id INTEGER,
 chat_id INTEGER, 
 .. ,
 PRIMARY KEY (site_id, chat_id)
);

DEFAULT values

By default each D1 table column allows NULL values and if we omit that column in an INSERT statement, the column value will remain NULL.

This behavior can be modified by explicitly defining a DEFAULT column value, which will be applied when we skip that column or insert NULL into it, example usage of the DEFAULT constraint is

CREATE TABLE messages (
...
  message TEXT DEFAULT 'N/A',
  score INTEGER DEFAULT 0
);

UNIQUE constraint

The UNIQUE constraint disallows duplicate values in the column. Additionally we may specify behavior to be applied when duplicates are tried to be inserted. The base syntax is:

<column name> <data type> UNIQUE [ON CONFLICT { ROLLBACK | ABORT | FAIL | IGNORE | REPLACE} ]

The optional ON CONFLICT clause specifies what should be done when we try to put duplicate values in the column.
ROLLBACK (default one), ABORT and FAIL – will discard our INSERT or UPDATE statement containing duplicate value and will also raise an error.
IGNORE – will discard the INSERT or UPDATE statement without errors.
REPLACE – will replace the old row with the newly inserted one.

examples, with unique column

CREATE TABLE test ( unique_col1 INTEGER NOT NULL UNIQUE )
CREATE TABLE test ( unique_col1 INTEGER NOT NULL UNIQUE ON CONFLICT REPLACE)

NOT NULL constraint

By default each D1 table column will allow NULL values (except the one marked with PRIMARY KEY). The NOT NULL constraint disallows NULL values to be entered in that column and if we try to insert NULL values there, an error will be throws.

The syntax for specifying NOT NULL constraint is:

CREATE TABLE test (
...
col1 INTEGER NOT NULL,
);

FOREIGN KEY constraint

The FOREIGN KEY constraint defines an explicit relation between two tables, that the Cloudflare D1 database will govern for us.

The idea of the foreign key constraint is that in column(s) of table where we define the foreign key, we want to allow only values that already exist in the foreign tables’ column(s) (or NULL if NULLs are allowed). If we try to INSERT value that is outside of the existing in the referenced table’s column(s) an error will be throw by D1.

the base syntax is:

CREATE TABLE <table name> (
...
  FOREIGN KEY <foreign key name> ( <col1< [, <col2> ...] ) 
  REFERENCES <foreign table name> ( <foreign col1> [, <foreign col2> ...] )
);

example (a foreign key that links the sentiments table with the chats table)

CREATE TABLE sentiments (
 sentiment_is INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, 
 sentiment INTEGER NOT NULL,
 chat_id INTEGER NOT NULL,
 FOREIGN KEY fk_chats (chat_id) REFERENCES chats (chat_id)
);

Additional options for FOREIGN KEY

The FOREIGN KEY constraint can be customized further by specifying actions to be performed by D1 when we try to UPDATE or DELETE values that referenced by rows of the initial table where the FOREIGN KEY relation is defined. Those actions are specified with the syntax
ON UPDATE action
ON DELETE action

example

CREATE TABLE sentiments (
 sentiment_is INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, 
 sentiment INTEGER NOT NULL,
 chat_id INTEGER NOT NULL,
 FOREIGN KEY fk_chats (chat_id) REFERENCES chats (chat_id) 
  ON UPDATE SET NULL 
  ON DELETE CASCADE
);

Possible values for action are SET NULL, SET DEFAULT, NO ACTION, RESTRICT and CASCADE
SET NULL – sets the column(s) of the referencing table (in our example table sentiments) (where the FOREIGN KEY is defined) to NULL
SET DEFAULT – sets the default value of the columns
NO ACTION – the referencing table is not modified
RESTRICT (default one) – an error is thrown
CASCADE – a cascade action is taken, e.g. chats.chat_id was 4 but gets updated to 5, automatically without any intervention from our side in sentiments.chat_id is SET to 5 in the rows where it was 4.

DROP TABLE command

The DROP TABLE SQL command erases a whole D1 database table with all its data. The common syntax is:

DROP TABLE <table name>;

example

DROP TABLE sentiments;

Although D1 has a time-travel functionality, we encourage you to be cautious when executing the DROP command.

Summary

This tutorial was about Cloudflare D1 CREATE TABLE and DROP table commands. It is by no means a complete work, but you can use it as a head starting point for creating your D1 database schemas.

If you prefer a more visual representation of your database, we would encourage you to try our D1Admin tool that can create tables in a point and click fashion.