SQL Reference Guide

CREATE TABLE

Description

Creates a table definition. A table definition consists of a list of column definitions that make up a table row. FairCom DB SQL provides two forms of the CREATE TABLE statement. The first form explicitly specifies column definitions. The second form, with the AS query_expression clause, implicitly defines the columns using the columns in the query expression.

Syntax

CREATE TABLE [ IF NOT EXISTS ] [ owner_name. ] table_name

( column_definition [ , { column_definition | table_constraint } ] ... )

[ STORAGE_ATTRIBUTES 'attributes' ]

;

CREATE TABLE [ IF NOT EXISTS ] [ owner_name. ] table_name

[ ( column_name [NULL | NOT NULL], ... ) ]

[ STORAGE_ATTRIBUTES 'attributes' ]

AS query_expression

;

column_definition ::

column_name data_type

[ DEFAULT { literal | USER | NULL | UID

| SYSDATE | SYSTIME | SYSTIMESTAMP | AUTOTIMESTAMP | AUTOTIMESTAMP_INSERT| AUTOTIMESTAMP_UPDATE | AUTOCHANGEID} ]

| [ IDENTITY [(seed, increment)] | auto_increment [(seed, increment)] ]

[ column_constraint [ column_constraint ... ] ]

Arguments

IF NOT EXISTS

This argument avoids failure by creating the table only if a table of the same name does not already exist.

owner_name

Specifies the owner of the table. If the name is different from the user name of the user executing the statement, then the user must have DBA privileges.

table_name

Names the table definition. FairCom DB SQL defines the table in the database named in the last CONNECT statement.

column_name data_type

Names a column and associates a data type with it. The column names specified must be different than other column names in the table definition. The data_type must be one of the supported data types described in Data Types.

DEFAULT

Specifies an explicit default value for a column. The column takes on the value if an INSERT statement does not include a value for the column. If a column definition omits the DEFAULT clause, the default value is NULL.

The DEFAULT clause accepts the following arguments:

literal	An integer, numeric or string constant.
USER	The name of the user issuing the INSERT or UPDATE statement on the table. Valid only for columns defined with character data types.
NULL	A null value.
UID	The user id of the user executing the INSERT or UPDATE statement on the table.
SYSDATE	The current date. Valid only for columns defined with DATE data types.
SYSTIME	The current time. Valid only for columns defined with TIME data types.
SYSTIMESTAMP	The current date and time. Valid only for columns defined with TIMESTAMP data types.
AUTOTIMESTAMP	Set to the current GMT date and time on update or inserts. Valid only for columns defined with TIMESTAMP data types. The column is not editable.
AUTOTIMESTAMP_INSERT	Set to the current GMT date and time on inserts. Valid only for columns defined with TIMESTAMP data types. The column is not editable.
AUTOTIMESTAMP_UPDATE	Set to the current GMT date and time on updates. Valid only for columns defined with TIMESTAMP data types. The column is not editable.
AUTOCHANGEID	Set the column to the change id value. Valid only for BIGINT columns. The column is not editable.

IDENTITY

Specifies that the column is an IDENTITY column. An IDENTITY column is one for which the data is not provided by the user but it is automatically generated by the DBMS. There can be only one identity column for a table. A default value cannot be specified for an identity column. IDENTITY can only be specified on columns of type INTEGER, SMALLINT, TINTYINT and BIGINT.

seed

Specifies a value that used for the identity column when the first row is inserted into the table. Specifying a seed value is optional. If a seed value is not specified, then it defaults to 1.

increment

Specifies a value by which the identity value of the previous row is incremented, which is then used for the identity column when the second and subsequent rows are inserted into the table. Specifying the increment value is optional. If it is not specified, then it defaults to 1.

Note: Although both the seed and increment values are optional, they must both be specified or both be left unspecified. It is not possible to specify only one of them.

auto_increment

auto_increment is specified and behaves exactly like IDENTITY, however, values can be automatically overridden from the INSERT statement. When an INSERT statement specifies a value for an IDENTITY column, it inserts the specified value; when the value is omitted, it is generated automatically. This provides flexibility many ORM provides such as SQL Alchemy depend on.

column_constraint

Specifies a constraint that applies while inserting or updating a value in the associated column. For more information, see Column Constraints.

table_constraint

Specifies a constraint that applies while inserting or updating a row in the table. For more information, see Table Constraints.

STORAGE_ATTRIBUTES 'attributes'

Specifies a string describing underlying FairCom DB table storage attributes. For more information see Storage Attributes.

AS query_expression

Specifies a query expression to use for the data types and contents of the columns for the table. The types and lengths of the columns of the query expression result become the types and lengths of the respective columns in the table created. The rows in the resultant set of the query expression are inserted into the table after creating the table. In this form of the CREATE TABLE statement, column names are optional.

If omitted, the names for the table columns are also derived from the query expression. For more information, see Query Expressions.

Examples

In the following example, the user issuing the CREATE TABLE statement must have REFERENCES privilege on the column “itemno” of the table “john.item”.

CREATE TABLE supplier_item (

supp_no INTEGER NOT NULL PRIMARY KEY,

item_no INTEGER NOT NULL REFERENCES john.item (itemno),

qty INTEGER

) ;

The following CREATE TABLE statement explicitly specifies a table owner, admin:

CREATE TABLE admin.account (

account integer,

balance money (12),

info char (84)

) ;

The following example shows the AS query_expression form of CREATE TABLE to create and load a table with a subset of the data in the customer table:

CREATE TABLE admin.dealer (name, street, city, state)

SELECT name, street, city, state

FROM customer

WHERE customer.state IN ('CA','NY', 'TX') ;

The following example includes a NOT NULL column constraint and DEFAULT clauses for column definitions:

CREATE TABLE emp (

empno integer NOT NULL,

deptno integer DEFAULT 10,

join_date date DEFAULT NULL

) ;

Authorization

The user executing this statement must have either DBA or RESOURCE privilege. If the CREATE TABLE statement specifies a foreign key that references a table owned by a different user, the user must have the REFERENCES privilege on the corresponding columns of the referenced table.

The AS query_expression form of CREATE TABLE requires the user to have select privilege on all the tables and views named in the query expression.

SQL Compliance	SQL-92, ODBC Minimum SQL grammar. Extensions: AS query_expression
Environment	Embedded SQL, interactive SQL, ODBC applications
Related Statements	DROP TABLE, ALTER TABLE, Query Expressions

Column Constraints

Description

Specifies a constraint for a column that restricts the values that the column can store. INSERT, UPDATE, or DELETE statements that violate the constraint fail. FairCom DB SQL returns a constraint violation error with SQLCODE -20116.

Column constraints are similar to table constraints but their definitions are associated with a single column.

Syntax

column_constraint ::

UNIQUE

| NOT NULL [ PRIMARY KEY | UNIQUE ]

| REFERENCES [ owner_name. ] table_name [ ( column_name ) ]

| CHECK ( search_condition )

Arguments

NOT NULL

Restricts values in the column to values that are not null.

NOT NULL PRIMARY KEY

PRIMARY KEY is a valid abbreviation of NOT NULL PRIMARY KEY.

Defines the column as the primary key for the table. There can be only one primary key for a table. A column with the NOT NULL PRIMARY KEY constraint cannot contain null or duplicate values. Other tables can name primary keys as foreign keys in their REFERENCES clauses.

If other tables name primary keys in their REFERENCES clauses, FairCom DB SQL restricts operations on the table containing the primary key:

DROP TABLE statements that delete the table fail
DELETE and UPDATE statements that modify values in the column that match a foreign key’s value also fail

The following example shows the creation of a primary key column on the table supplier.

CREATE TABLE supplier (

supp_no INTEGER NOT NULL PRIMARY KEY,

name CHAR (30),

status SMALLINT,

city CHAR (20)

) ;

NOT NULL UNIQUE

Defines the column as a unique key that cannot contain null or duplicate values. Columns with NOT NULL UNIQUE constraints defined for them are also called candidate keys.

Other tables can name unique keys in their REFERENCES clauses. If they do, FairCom DB SQL restricts operations on the table containing the unique key:

DROP TABLE statements that delete the table fail
DELETE and UPDATE statements that modify values in the column that match a foreign key’s value also fail

The following example creates a NOT NULL UNIQUE constraint to define the column ss_no as a unique key for the table employee:

CREATE TABLE employee (

empno INTEGER NOT NULL PRIMARY KEY,

ss_no INTEGER NOT NULL UNIQUE,

ename CHAR (19),

sal NUMERIC (10, 2),

deptno INTEGER NOT NULL

) ;

REFERENCES table_name [ (column_name) ]

Defines the column as a foreign key and specifies a matching primary or unique key in another table. The REFERENCES clause names the matching primary or unique key.

A foreign key and its matching primary or unique key specify a referential constraint: A value stored in the foreign key must either be null or be equal to some value in the matching unique or primary key.

You can omit the column_name argument if the table specified in the REFERENCES clause has a primary key and you want the primary key to be the matching key for the constraint.

The following example defines order_item.orditem_order_no as a foreign key that references the primary key orders.order_no.

CREATE TABLE orders (

order_no INTEGER NOT NULL PRIMARY KEY,

order_date DATE

) ;

CREATE TABLE order_item (

orditem_order_no INTEGER REFERENCES orders ( order_no ),

orditem_quantity INTEGER

) ;

Note that the second CREATE TABLE statement in the previous example could have omitted the column name order_no in the REFERENCES clause, since it refers to the primary key of table orders.

CHECK (search_condition)

Specifies a column-level check constraint. FairCom DB SQL restricts the form of the search condition. The search condition must not:

Refer to any column other than the one with which it is defined
Contain aggregate functions, subqueries, or parameter references

The following example creates a check constraint:

CREATE TABLE supplier (

supp_no INTEGER NOT NULL,

name CHAR (30),

status SMALLINT,

city CHAR (20) CHECK (supplier.city <> 'MOSCOW')

) ;

Table Constraints

Description

Specifies a constraint for a table that restricts the values that the table can store. INSERT, UPDATE, or DELETE statements that violate the constraint fail. FairCom DB SQL returns a Constraint violation error.

Table constraints have syntax and behavior similar to column constraints. Note the following differences:

The syntax for table constraints is separated from column definitions by commas.
Table constraints must follow the definition of columns they refer to.
Table constraint definitions can include more than one column and FairCom DB SQL evaluates the constraint based on the combination of values stored in all the columns.

Syntax

table_constraint ::

PRIMARY KEY ( column [, ... ] )

| UNIQUE ( column [, ... ] )

| FOREIGN KEY ( column [, ... ] )

REFERENCES [ owner_name. ] table_name [ ( column [, ... ] ) ]

| CHECK ( search_condition )

Arguments

PRIMARY KEY ( column [, ... ] )

Defines the column list as the primary key for the table. There can be at most one primary key for a table.

All the columns that make up a table-level primary key must be defined as NOT NULL, or the CREATE TABLE statement fails. The combination of values in the columns that make up the primary key must be unique for each row in the table.

Other tables can name primary keys in their REFERENCES clauses. If they do, FairCom DB SQL restricts operations on the table containing the primary key:

DROP TABLE statements that delete the table fail
DELETE and UPDATE statements that modify values in the combination of columns that match a foreign key’s value also fail

The following example shows creation of a table-level primary key. Note that its definition is separated from the column definitions by a comma:

CREATE TABLE supplier_item (

supp_no INTEGER NOT NULL,

item_no INTEGER NOT NULL,

qty INTEGER NOT NULL DEFAULT 0,

PRIMARY KEY (supp_no, item_no)

) ;

UNIQUE ( column [, ... ] )

Defines the column list as a unique, or candidate, key for the table. Unique key table-level constraints have the same rules as primary key table-level constraints, except that you can specify more than one UNIQUE table-level constraint in a table definition.

The following example shows creation of a table with two UNIQUE table-level constraints:

CREATE TABLE order_item (

order_no INTEGER NOT NULL,

item_no INTEGER NOT NULL,

qty INTEGER NOT NULL,

price MONEY NOT NULL,

UNIQUE (order_no, item_no),

UNIQUE (qty, price)

) ;

FOREIGN KEY ... REFERENCES

Defines the first column list as a foreign key and, in the REFERENCES clause, specifies a matching primary or unique key in another table.

A foreign key and its matching primary or unique key specify a referential constraint: The combination of values stored in the columns that make up a foreign key must either:

Have at least one of the column values be null
Be equal to some corresponding combination of values in the matching unique or primary key

You can omit the column list in the REFERENCES clause if the table specified in the REFERENCES clause has a primary key and you want the primary key to be the matching key for the constraint.

The following example defines the combination of columns student_courses.teacher and student_courses.course_title as a foreign key that references the primary key of the table courses. Note that the REFERENCES clause does not specify column names because the foreign key refers to the primary key of the courses table.

CREATE TABLE courses (

teacher CHAR (20) NOT NULL,

course_title CHAR (30) NOT NULL,

PRIMARY KEY (teacher, course_title)

) ;

CREATE TABLE student_courses (

student_id INTEGER,

teacher CHAR (20),

course_title CHAR (30),

FOREIGN KEY (teacher, course_title) REFERENCES courses

) ;

FairCom DB SQL evaluates the referential constraint to see if it satisfies the following search condition:

(student_courses.teacher IS NULL

OR student_courses.course_title IS NULL)

EXISTS (SELECT * FROM student_courses WHERE

(student_courses.teacher = courses.teacher AND

student_courses.course_title = courses.course_title)

)

INSERT, UPDATE or DELETE statements that cause the search condition to be false violate the constraint, fail, and generate an error.

CHECK (search_condition)

Specifies a table-level check constraint. The syntax for table-level and column level check constraints is identical. Table-level check constraints must be separated by commas from surrounding column definitions.

FairCom DB SQL restricts the form of the search condition. The search condition must not:

Refer to any column other than columns that precede it in the table definition
Contain aggregate functions, subqueries, or parameter references

The following example creates a table with two column-level check constraints and one table-level check constraint:

CREATE TABLE supplier (

supp_no INTEGER NOT NULL,

name CHAR (30),

status SMALLINT CHECK (

supplier.status BETWEEN 1 AND 100 ),

city CHAR (20) CHECK (

supplier.city IN ('NEW YORK', 'BOSTON', 'CHICAGO')),

CHECK (supplier.city <> 'CHICAGO' OR supplier.status = 20)

) ;

Storage Attributes

A quoted string that specifies specific FairCom DB SQL table attributes. FairCom DB SQL supports the following STORAGE_ATTRIBUTES parameters:

'ENCR=AES32' - Turns on encryption for the table. Omit 'ENCR=AES32' from the STORAGE_ATTRIBUTES clause when you do not want to encrypt the table. Note: if encryption is not yet enabled for the database, this setting will cause table creation to fail with error(-17454): CT - Service not supported. To enable encryption for the database, see Advanced Data Encryption in the Server Administrators Guide.
- AES16 (Rijndael)
- AES24
- AES32
- DES8
- DES16
- DES24
- BLF8 through BLF56 (Blowfish)
- TWF16 (Twofish)
- TWF24
- TWF32
'RECBYT_IDX' - Creates the table with a RECBYT index. This index is required for physical backward traversal of variable-length type files. The RECBYT index does impose a minimal amount of overhead, however, when inserting/updating/deleting records. This index is not required for usual operations, and as such, is off by default.
'NORECBYT_IDX' - (Default) Creates the table without a RECBYT index.
'ROWID_FLD' - (Default) The ROWID field is an auto-incrementing number which takes advantage of the c-tree serial segment index mode. This hidden field is a sequential number added to each record insert, and maintained with an associated index. This field is not required for proper SQL operation, however, is referenced directly by the ROWID related scalar functions. In the case of no ROWID field, the record RECBYT value is returned, which may not maintain uniqueness over time. As there is a limit of only one serial segment index per c-tree data file, this value is unavailable for other fields when a ROWID is present. Conversely, no ROWID is available when an existing c-tree data file with a serial segment field is imported into FairCom DB SQL. The IDENTITY field attribute should be used for auto-incrementing values as it is better performing with greater field type flexibility.
'NOROWID_FLD' - Creates a table without the ROWID serial segment field and index.
'PREIMG' - This option disables transaction logging for the table. This can be useful for temporary FairCom DB SQL tables that are not required to be recovered in the event of catastrophic failure, yet retain atomicity of transactions.
'HOTALTER' - Creates the table with Hot Alter Table support. (Supported in V11.5 and later.)
'NOHOTALTER' - (default) - Create the table with Hot Alter Table support disabled. (Supported in V11.5 and later.)
'HUGE' - (Default) Denotes that a table should be created as a c-tree HUGE file (64-bit file offset addressing).
'NOHUGE' - Denotes that a table should be created as a c-tree non-HUGE file (32-bit file offset addressing). These files have a limit of 2Gb or 4Gb depending on your OS platform.
Note: See the SQL_OPTION NO_HUGEFILE FairCom Server configuration option to make NOHUGE the default.
'NOMSEC' - Denotes that a table containing time and timestamp fields should be created with these fields not having milliseconds support.
‘BLOCK_COMPRESSION' - Denotes that a table should be created with block compression.
‘CHANGEID’ - Creates the table with a ‘changeid’ field to make the table compliant with JSON DB expectations. To ensure a SQL CREATE TABLE statement adds a 'changeid' field to a table, include the 'changeid' keyword in the STORAGE_ATTRIBUTES clause. This feature overrides the SQL_OPTION AUTO_CHANGEID setting in ctsrvr.cfg.
‘NOCHANGEID’ - Creates the table without the ‘changeid’ field. To ensure a SQL CREATE TABLE statement does not add a 'changeid' field to a table, include the 'nochangeid' keyword in the STORAGE_ATTRIBUTES clause. This feature overrides the SQL_OPTION AUTO_CHANGEID setting in ctsrvr.cfg.
‘CTMONEY’ - Creates the table having the SQL money type mapped to c-tree CT_MONEY instead of the default of either CT_CURRENCY or CT_NUMBER based on the precision and scale. Mapping to CT_MONEY implies that it is not possible to use a scale of 4 and despite the precision definition in SQL the maximum value that can be stored is what fits in a LONG.

Examples

Setting a single storage attribute only requires the attribute name to be included between the single quotes following the STORAGE_ATTRIBUTES clause. Double quotes cannot be used:

CREATE TABLE test1 ( id integer ) STORAGE_ATTRIBUTES 'HUGE';

To set multiple storage attributes, separate them with a semicolon. You may include space before or after the semicolon, but it is unnecessary. The last attribute may be followed by a semicolon or not:

CREATE TABLE test2 ( id integer ) STORAGE_ATTRIBUTES 'RECBYT_IDX;ROWID_FLD';

Storage attributes are not case sensitive.

CREATE TABLE test3 ( id integer ) STORAGE_ATTRIBUTES 'recbyt_idx;rowid_fld;hotalter;huge;';

The following attributes cannot be used together:

RECBYT_IDX and NORECBYT_IDX
ROWID_FLD and NOROWID_FLD
HOTALTER and NOHOTALTER
HUGE and NOHUGE
CHANGEDID and NOCHANGEID

The following example throws error(-26014): conflicting or duplicate attributes found:

CREATE TABLE test4 ( id integer ) STORAGE_ATTRIBUTES 'huge;noHuge';

Encryption must be enabled in the database before you can use an encryption attribute. When encryption is not enabled, the following example throws error(-17454): CT - Service not supported:

CREATE TABLE test5 ( id integer ) STORAGE_ATTRIBUTES 'ENCR=AES32';

NOTE: The default storage attributes are best unless you need to turn on encryption or turn off transaction processing for a table. You have limited ability to change storage attributes after a table is created because most attributes define how the table is physically created. You may change transaction logging for a table (i.e. PREIMG) using the cttrnmod utility or call the stored procedure fc_set_file_tran_state. This is useful when you want to temporarily suspend transaction processing for a bulk load or to turn it on temporarily for replication. You can also create a new table with the desired storage attributes, copy the old table’s data into it, rename the old table, and rename the new table. When all is working as desired, you can drop the old table. In the SQL Reference Guide, see CREATE TABLE, ALTER TABLE, and DROP TABLE.

The following example insures that a 'changeid' field will be added to the table.

CREATE TABLE test6 ( id integer ) STORAGE_ATTRIBUTES 'hotalter;huge;changeid;';

The following example insures that a 'changeid' field will NOT be added to the table.

CREATE TABLE test7 ( id integer ) STORAGE_ATTRIBUTES 'hotalter;huge;nochangeid;';

The following example assigns 'changeid' functionality to a specified field.

CREATE TABLE test8 ( id bigint, change_tracker bigint default autochangeid);

Deprecated Attributes

‘CAMO’ - A lightweight option for obscuring data. This option gives a small amount of protection from casual inspection of the c-tree data and index files. CAMO or "Camouflage" is an older, legacy method of hiding data, which is not a standards-conforming encryption scheme, such as AES. It is not intended as a replacement for Advanced Encryption or other security systems.
'ENCR=crypt' - The following crypt values for 'ENCR=crypt' are deprecated.
- AES16
- AES24
- DES8
- DES16
- DES24
- BLF8
- BLF56
- TWF16
- TWF24
- TWF32

SQL - Create [if not exists] and Drop [if exists]

The CREATE TABLE syntax has been expanded to avoid failure in case the table exists (during create) or does not exist (during drop). The feature is similar to syntaxes found in the MySql and Postgres dialects.

We now implement this feature for tables, indexes, procedures, triggers, and functions:

CREATE TABLE [IF NOT EXISTS]...

DROP TABLE [IF EXISTS]...

CREATE INDEX [IF NOT EXISTS]...

DROP INDEX [IF EXISTS]...

CREATE PROCEDURE [IF NOT EXISTS]...

DROP PROCEDURE [IF EXISTS]...

CREATE TRIGGER [IF NOT EXISTS]...

DROP TRIGGER [IF EXISTS]...

CREATE FUNCTION [IF NOT EXISTS]...

DROP FUNCTION [IF EXISTS]...

Assign Values to Auto-Increment Fields in INSERT

SQL - auto_increment fields enhanced

IDENTITY column support is now "smart" and automatically inserts values when explicitly specified and auto-generates values when they are not (the default existing behavior).

The syntax/definition of auto_increment fields in this release is identical to the current IDENTITY syntax except INSERT statements are smarter. When an INSERT statement specifies a value for an IDENTITY column, it inserts the specified value; when the value is omitted, it is generated automatically.

Prior to this modification, it was necessary to explicitly disable IDENTITY insertion with
SET identity_insert <table> on | off.

Only one table at a time can be set to identity_insert on.

No table definition changes are required to enable this new support. It enables compatibility with many external SQL frameworks such as SQLAlchemy.