CREATE TABLE

Defines a new table and prepares it for the receiving data. By default it creates permanent table on disk. You can also specify to create a temporary table, in-memory table, or table as result of SELECT query.

table_definition
    :    CREATE [OR REPLACE] [ {GLOBAL | LOCAL} TEMPORARY] {[RAM] | [SYSTEM]} 
         TABLE [IF NOT EXISTS] table_name
         { table_element_list [ON COMMIT {DELETE | PRESERVE} ROWS ]
         | AS select_statement 
         }

table_element_list
    :    ( table_element, ... ) 

table_element
    :    column_definition 
    |    table_constraint 

column_definition
    :    column_name data_type [ default_clause ] [ column_constraint, ... ]

column_constraint
    :    [ CONSTRAINT constraint_name ] 
         {    NOT NULL 
         |    NULL 
         |    INDEXED 
         |    HASH                                            -- added in v5.6
         |    WORDS 
         |    COMPRESSED 
         |    TEMPORARY                                       -- added in v4.0
         |    { PRIMARY KEY | UNIQUE } 
         |    { AUTOINCREMENT | AUTO_INCREMENT }
         |    IDENTITY  
         |    CHECK ( search_condition ) 
         |    references_specification 
         |    METHOD( STRING_LITERAL )  
         } 

table_constraint_definition
    :    [ CONSTRAINT constraint_name ] 
         {    table_constraint_unique_definition
         |    table_constraint_referential_definition  
         |    table_constraint_check_definition
         |    table_constraint_index_definition 
         }

table_constraint_unique_definition
    :    { UNIQUE [ index_name ]  |  PRIMARY KEY } [ index_method ] index_column_list 

table_constraint_referential_definition
    :    FOREIGN KEY ( column_name_list ) references_specification

table_constraint_check_definition
    :    CHECK ( search_condition )

table_constraint_index_definition
    :    INDEX [ index_name ] [ index_method ] index_column_list

default_clause
    :    DEFAULT default_option
 
default_option
    :    NULL
    |    USER | CURRENT_USER | SESSION_USER | SYSTEM_USER 
    |    METHOD( STRING_LITERAL ) 
    |    literal 

references_specification
    :    REFERENCES table_name [ ( column_name_list ) ]
         [ MATCH {FULL | PARTIAL} ] 
         [ ON DELETE referential_action ]
         [ ON UPDATE referential_action ]

referential_action
    :    CASCADE | SET NULL | SET DEFAULT | NO ACTION | RESTRICT

Fields of the new table are mentioned as a list enclosed in brackets in the command CREATE TABLE. The elements of this list are comma-separated. The order of the fields defines their physical order in the table:

• field's name
• field's data type
• the possibility to store NULL value.

Also, you can use several extra elements in the field definition:

• to limit the data value in the field;
• to define that the the field should contain unique values or be primary or foreign key.

CREATE TABLE offices
      ( office INTEGER NOT NULL,
        city    VARCHAR (15) NOT NULL,
        region  VARCHAR (10) NOT NULL,
        product CHAR    (5)  NOT NULL )

This clause forces dropping of an existed table with the specified name and then new empty table will be create as specified. So effectively this is the same as

DROP TABLE T;
CREATE TABLE T ....

ATTENTION: 'CREATE OR REPLACE' will destroy all sub-objects of existed table, such as triggers, constraints.

If table do not exists, then nothing happens.

NOTE: 'OR REPLACE' syntax Valentina have take from Oracle/Postgre world.

The IF NOT EXISTS option allows you supress the error message in case if such table already exists. This makes it much easier to perform SQL dumps without interruption.

NOTE: 'IF NOT EXISTS' syntax Valentina have take from mySQL world.

This allows you specify that you want create TEMPORARY table. If RAM is specified then you get TEMPORARY table on RAM.

GLOBAL/LOCAL are ignored by Valentina SQL parser.

But take the following rules:

• On default temporary table in the Valentina Server is global.
• To get LOCAL TEMPORARY table you need use '#'

-- creates permanent table on disk, visible for all users.
CREATE TABLE T1(f1 INTEGER); 

-- Create GLOBAL TEMPORARY table on disk, visible for all users:
CREATE GLOBAL TEMPORARY TABLE T1(f1 INTEGER); 
CREATE TEMPORARY TABLE T1(f1 INTEGER); 

-- creates LOCAL TEMPORARY table on disk, visible only for this user only:
CREATE LOCAL TEMPORARY TABLE #T1(f1 INTEGER);
CREATE TEMPORARY TABLE #T1(f1 INTEGER);
CREATE TABLE #T1(f1 INTEGER); 

-- creates LOCAL TEMPORARY Table on RAM, visilbe for this user only:
CREATE LOCAL TEMPORARY RAM TABLE #T1(f1 INTEGER); 
CREATE RAM TABLE #T1(f1 INTEGER); 

Fields go as a coma-separated list. Parameters of a field are separated by spaces. Detailed description of each Valentina field type you can in the Valentina Kernel Manual → Field section.

CREATE TABLE T(
    fldAge    ULONG NOT NULL INDEXED,
    fldHeight USHORT 
);

For fixed-string and VarChar field you need specify maximal length and you have few special string parameters, such as indexing by 'WORDS'.

For TEXT fields can be used flag 'COMPRESSED' to force engine use ZIP compression for this text field. Such field still can be indexed and searched.

CREATE TABLE T(
    fldFirstName String(50) NOT NULL,
    fldLastName  VARCHAR(2044) NOT NULL INDEXED WORDS,
    fldBiography TEXT(1024) INDEXED WORDS COMPRESSED    // READ about BLOBs below also.
);

BLOB, Picture and TEXT fields instead of max_length can have segment_size parameter.

CREATE TABLE T(
    fldPhoto     Picture(4096) NOT NULL,              // 4096 IS NOT MAX SIZE FOR BLOB, this IS segment SIZE!  
    fldBiography TEXT(1024) INDEXED WORDS COMPRESSED  // 1024 IS NOT MAX SIZE FOR TEXT, this IS segment SIZE!
);

ObjectPtr field is a special dual field-link entity of Valentina Data Model.

If you are used to Relational model you can con side ObjectPtr as mix of ULONG field and Foreign Key constraint. This field always is linked to RecID field of a Valentina Table, this is why in REFERENCES part we need specify only table name. Besides, for ObjectPtr link do not have sense ON UPDATE part of FK constraint.

CREATE TABLE tblPhone(
    fldNumber    String(12) NOT NULL,  
    fldPersonPtr ObjectPtr CONSTRAINT linkPerson_Phone REFERENCES tblPerson ON DELETE SET NULL
);

A Field of Numeric, DateTime, String and TEXT types can be made as a virtual calculated field just by specifying METHOD formula. The formula can be any valid Valentina SQL expression .

Such calculated field is not stored on disk, but can be yet indexed. You can read details in the Valentina Kernel Manual here.

CREATE TABLE tblPhone(
    fldFirstName VARCHAR(2044) INDEXED,
    fldLastName VARCHAR(2044) INDEXED,
 
    mfFullName VARCHAR(2044) METHOD( 'fldFirstName || fldLastName' ) 
);

Unique feature for Valentina define TEMPORARY field of a table, because Valentina can do this thanks to columnar format of table. Usually this have sense in the ALTER TABLE only.

CREATE TABLE tblPhone(
    fldFirstName VARCHAR(2044) INDEXED,
    fldLastName VARCHAR(2044) INDEXED,
 
    fldMyHas FixedBinary(16) TEMPORARY RAM   // stored NOT IN the .dat volume but IN the .tmp volume 
);

Starting with v5 you can also define TEMPORARY RAM fields. This can be interesting feature in combination with another new feature v5 - triggers on database event

CREATE TRIGGER trig_after_open_CreatePersonHas 
  AFTER STARTUP ON DATABASE
  BEGIN
      ALTER TABLE T ADD COLUMN fldMyHash FixedBinary(16) TEMPORARY RAM;
  END

A CONSTRAINT can be one of the following:

• a column-level constraint
• a table-level constraint

Column-level constraints refer to a single column in the table and do not specify a column name (except check constraints). They refer to the column that they follow.

Table-level constraints refer to one or more columns in the table. Table-level constraints specify the names of the columns to which they apply. Table-level CHECK constraints can refer to 0 or more columns in the table.

Column constraints and table constraints have the same function; the difference is in where you specify them. Table constraints allow you to specify more than one column in a PRIMARY KEY, UNIQUE, CHECK, or FOREIGN KEY constraint definition. Column-level constraints (except for check constraints) refer to only one column.

NULL | NOT NULL keywords determine possibility to store NULL values in the field. If the field is for definite data only then NOT NULL should be used.

CREATE TABLE offices
    ( office INTEGER NOT NULL,
      city   VARCHAR (15) NOT NULL,
      region VARCHAR (10) NOT NULL,
      mgr    INTEGER,
      target INTEGER,
      sales  INTEGER NOT NULL )

DEFAULT keyword defines the value which will be used during INSERT if no explicit value mentioned.

CREATE TABLE offices
    ( office INTEGER NOT NULL,
      city   VARCHAR (15) NOT NULL,
      region VARCHAR (10) NOT NULL DEFAULT ‘Eastern’,
      mgr    INTEGER DEFAULT '106',
      target INTEGER DEFAULT NULL,
      sales  INTEGER NOT NULL DEFAULT '0.00' )

When inserting a record, you can skip any field with a default value. This assumes that you specify office and city only. As a result, the remaining fields will be filled using default values. “Eastern” - for region, 106 - for mgr and so on.

INSERT INTO offices( office, city )
       VALUES( 1, 'NY' )

Or you can use the DEFAULT keyword

INSERT INTO offices( office, city, region, mgr, target, sales )
       VALUES( 1, 'NY', DEFAULT, DEFAULT, DEFAULT, DEFAULT )

[NEW in 5.0]

You can define the whole expression using DEFAULT METHOD('expr') form. You can use in this case only expressions that do not contain other fields.

• now()
• current_user_name()
• UUID()
• nextval( seq_name )
• …

The primary key constraint specifies that a column or columns of a table can contain only unique (non-duplicate), nonnull values. Technically, PRIMARY KEY is merely a combination of UNIQUE and NOT NULL, but identifying a set of columns as primary key also provides metadata about the design of the schema, as a primary key implies that other tables can rely on this set of columns as a unique identifier for rows.

Only one primary key can be specified for a table, whether as a column constraint or a table constraint.

The primary key constraint should name a set of columns that is different from other sets of columns named by any unique constraint defined for the same table.

Examples:

Primary Key on a single column:

CREATE TABLE tblPerson(
   ID ULONG PRIMARY KEY 
)

Primary Key on few columns:

CREATE TABLE films (
    code        CHAR(5),
    title       VARCHAR(40),
    did         INTEGER,
    date_prod   DATE,
    kind        VARCHAR(10),
    len         INTERVAL HOUR TO MINUTE,
    CONSTRAINT code_title PRIMARY KEY(code,title)
);

Note: Valentina DB offers yet ObjectPtr and Binary Links to establish links between related tables. In many case-sensitive they are simpler for development and are more effective by storage size and speed.

Some relational DBMS have this option to provide the value of the primary key field to be created automatically every time a new record is inserted. Syntax may differ, for example:

• mySQL : ID int PRIMARY KEY AUTO_INCREMENT
• MS Access: ID int PRIMARY KEY AUTOINCREMENT
• MS SQL : ID int PRIMARY KEY IDENTITY(1,1)

In Valentina DB we support all 3 syntax with some simplification for MS SQL style:

• ID int PRIMARY KEY AUTO_INCREMENT
• ID int PRIMARY KEY AUTO_INCREMENT
• ID int PRIMARY KEY IDENTITY

Starting with v5.0 Valentina DB supports Sequences (similar to PostgreSQL), which you may prefer to use on few reasons. Also provided support of SERIAL32 and SERIAL64 pseudo-types for columns, that work as AUTOINCREMENT, but have even simpler syntax:

CREATE TABLE tblPerson(
    fldID SERIAL32   -- this means PRIMARY KEY + AUTOINCREMENT.  
)

Defines the Table Foreign Key and defines the Link, which is created between the above-mentioned two tables). This statement should have:

• the created Table column which forms the foreign key;
• the table-ancestor which is linked to this table as “parent-table” using this foreign key;
• optional name for this relation; it is not used in the SQL commands, but it can appear in the error diagnostics and it would be necessary further if the foreign key should be dropped;
• optional deletion rule for the given link (CASCADE, SET NULL, SET DEFAULT or NO ACTION), which defines the action we use on deletion of the parent record;
• optional updating rule for the given relation, which defines the action we use on update of the primary key in the parent-record.

DBMS checks whether primary and foreign keys corresponds each other. So the linked parent-table should be defined already.

If two or more tables have circle links then it is not possible to define the foreign key for the first of these tables because its parent table do not exists yet. DBMS will fail to execute CREATE TABLE command, and yield the message that the table definition contains the link on unexisted table. In this case it is necessary to create the table without the foreign key definition and to add it later with the help of ALTER TABLE command.

Note: Valentina DB offers yet ObjectPtr and Binary Links to establish links between related tables. In many case-sensitive they are simpler for development and are more effective by storage size and speed.

Unique conditions are defined in the UNIQUE statement.

CREATE TABLE offices
    ( office INTEGER NOT NULL,
      city   VARCHAR(15) NOT NULL, 
      UNIQUE(CITY)
    )

If unique constraint contains a single field only, it is allowed to use the short form of a constraint definition.

CREATE TABLE offices
    ( office INTEGER NOT NULL,
      city VARCHAR (15) NOT NULL UNIQUE 
    )

In contrast to PRIMARY KEY, the unique condition can be applied on several fields of the table and allow NULL values.

In the CREATE TABLE command you can define the CHEСK constraint, which restricts the values accepted by the field.

This condition is checked each attempt to change a field (with the help of INSERT or UPDATE commands). If the condition is true, then the change is allowed; otherwise the DBMS rejects the change and return the error message.

Example:

CREATE TABLE t1 ( f1 LONG, CONSTRAINT chk1 CHECK ( f1 > 10));
 
-- Ok
INSERT INTO t1 VALUES (15);
 
-- Kernel error: 0x23504. 
-- Check constraint violation occurred, table name = "t1", constraint name = "chk1".
INSERT INTO t1 VALUES (1);
 
SELECT * FROM t1;
-> 15

Read details about hash indexed here.

This form of CREATE TABLE allows you create a new table with structure similar to another table:

CREATE TEMPORARY TABLE tblPerson_tmp
  AS SELECT * FROM tblPerson WHERE FALSE;