CREATE TABLE

Clause in a schema template definition to create a table. Note that a table can either have a primary key defined from one or multiple columns, or it must be declared as a SINGLE ROW ONLY table, in which case only one row can be inserted into it. This latter type of table can be useful for things like database configuration parameters, which the application wants to inform its interpretation of all data in the database.

Syntax

Parameters

tableName

The name of the TABLE

columnName

The name of a column of the defined TABLE

columnType

The associated type of the column

primaryKeyColumnName

The name of the column to be part of the primary key of the TABLE

Examples

Table with a primary key

CREATE SCHEMA TEMPLATE TEMP
    CREATE TABLE T (A BIGINT NULL, B DOUBLE NOT NULL, C STRING, PRIMARY KEY(A, B))

-- On a schema that uses the above schema template
INSERT INTO T VALUES
    (NULL, 0.0, 'X'),
    (1, 1.0, 'A'),
    (NULL, 2.0, 'B');

SELECT * FROM T;

A	B	C
NULL	0.0	"X"
NULL	2.0	"B"
1	1.0	"A"

Table limited to a single row

CREATE SCHEMA TEMPLATE TEMP
    CREATE TABLE T (A BIGINT NULL, B DOUBLE NOT NULL, C STRING, SINGLE ROW ONLY)

-- On a schema that uses the above schema template
INSERT INTO T VALUES
    (NULL, 0.0, 'X')

SELECT * FROM T;

A	B	C
NULL	0.0	"X"

Attempting to insert a second row in a SINGLE ROW ONLY table will result in a UNIQUE_CONSTRAINT_VIOLATION error:

INSERT INTO T VALUES
    (1, 2.0, 'X')

SqlException(23505 - UNIQUE_CONSTRAINT_VIOLATION)

SELECT * FROM T;

A	B	C
NULL	0.0	"X"