Creating tables

Common examples of CREATE TABLE statements are presented below.

CREATE TABLE IF NOT EXISTS customers (
    id            INTEGER,
    customer_name VARCHAR[60],
    email         VARCHAR[150],
    address       VARCHAR,
    city          VARCHAR,
    ip            VARCHAR[40],
    country       VARCHAR[15],
    age           INTEGER,
    active        BOOLEAN,
    PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS products (
    id          INTEGER,
    product     VARCHAR NOT NULL,
    price       VARCHAR NOT NULL,
    created_at  TIMESTAMP,
    PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS orders (
    id          INTEGER AUTO_INCREMENT,
    customerid  INTEGER,
    productid   INTEGER,
    created_at  TIMESTAMP,
    PRIMARY KEY id
);

CREATE TABLE customer_review(
    customerid  INTEGER,
    productid   INTEGER,
    review      VARCHAR,
    created_at  TIMESTAMP,
    PRIMARY KEY (customerid, productid)
);

IF NOT EXISTS

With this clause the CREATE TABLE statement will not fail if a table with same name already exists.

Note: If the table already exists, it is not compared against the provided table definition neither it is updated to match it.

NOT NULL

Columns marked as not null can not have a null value assigned.

PRIMARY KEY

Every table in immudb must have a primary key. Primary key can use at least 1 and up to 8 columns.

Columns used in a primary key can not have NULL values assigned, even if those columns are not explicitly marked as NOT NULL.

Primary key creates an implicit unique index on all contained columns.

AUTO_INCREMENT

A single-column PRIMARY KEY can be marked as AUTO_INCREMENT. immudb will automatically set a unique value of this column for new rows.

When inserting data into a table with an INSERT statement, the value for such primary key must be omitted. When updating data in such table with UPSERT statement, the value for such primary key is obligatory and the UPSERT statement can only update existing rows.

The type of an AUTO_INCREMENT column must be INTEGER. Internally immudb will assign sequentially increasing values for new rows ensuring this value is unique within a single table.

Foreign keys

Explicit support for relations to foreign tables is not currently supported in immudb. It is possible however to create ordinary columns containing foreign key values that can be used in JOIN statements. Application logic is responsible for ensuring data consistency and foreign key constraints.

SELECT * FROM orders
INNER JOIN customers ON customers.id = orders.customerid
INNER JOIN products ON products.id = orders.productid;