SQL-J Language Reference
Page 16 of 121

CREATE TABLE statement

A CREATE TABLE statement creates a table. Tables contain columns and constraints, rules to which data must conform. Table-level constraints specify a column or columns. Columns have a data type and can specify column constraints (column-level constraints).

For information about constraints, see CONSTRAINT clause.

You can specify a default value for a column. A default value is the value to be inserted into a column if no other value is specified. If not explicitly specified, the default value of a column is NULL. See Column Defaults. You can also have Cloudscape automatically generate and increment an integer for a column. See Columns with Autoincrement Defaults.

NEW: Columns with autoincrement defaults are new in Version 3.6.

You can specify storage properties such as page size for a table with a PROPERTIES clause. See PROPERTIES clause.

The SET LOCKING clause allows you to override the locking granularity for a particular table. See Overriding Row-Level Locking.

Tables cannot be created in the SYS schema.

Syntax

CREATE TABLE TableName
    ( { ColumnDefinition | Table-Level Constraint}
    [ , { ColumnDefinition | Table-Level Constraint} ] * )
[ PROPERTIES clause ]
[ SET LOCKING = { TABLE | ROW } ]

ColumnDefinition

SimpleColumnName DataType
    [ [ WITH ] DEFAULT {
        ConstantExpression |
        NULL |
         AutoincrementDefaultDefinition
        }
    ]
    [ Column-Level Constraint ]*

AutoincrementDefaultDefinition

AUTOINCREMENT [ INITIAL ConstantIntegerLiteral ]
    [ INCREMENT ConstantIntegerLiteral ]

The syntax of DataType is described in Data Types.

The syntaxes of Column-Level Constraint and Table-Level Constraint are described in CONSTRAINT clause.

If the DEFAULT is AUTOINCREMENT, the column data type must be one of:

  • TINYINT
  • SMALLINT
  • INT
  • LONG

NEW: The DEFAULT type of AUTOINCREMENT is new in Version 3.6.

Column Defaults

For the definition of a default value, a ConstantExpression is an expression that does not refer to any table. It can include literals, built-in functions such as USER, CURRENT_DATE, CURRENT_TIME, and CURRENT_TIMESTAMP, java method invocations, java field references, and any other expression, as long as it does not refer directly to a table or to a column in a table. (If the default table contains a method call, the method may in fact contain SQL-J statements that refer to tables and columns.) A method call cannot contain dynamic parameters (?).

In a Cloudscape synchronization system, non-literal defaults are re-evaluated at the source if the insert or updated statement is executed within a work unit.

Columns with Autoincrement Defaults

For TINYINT, SMALLINT, INT, and LONG columns with a DEFAULT value of AUTOINCREMENT, Cloudscape automatically assigns increasing integer values to the column. Autoincrement column defaults behave like other defaults in that when an insert statement does not specify a value for the column, Cloudscape automatically provides the value. However, the value is not a constant--Cloudscape automatically increments the default value every time a row is inserted. Also, unlike other defaults, you are not allowed to insert a value directly into or update a column with an autoincrement default.

By default, the intial value for a column with an autoincrement default is 1, and the amount of the increment is 1. You can specify non-default values for both the initial value and the autoincrement amount when you define the column with the key words INITIAL and INCREMENT. And if you specify a negative number for the increment value, Cloudscape decrements the value with each insert.

The maximum and minimum values allowed in columns with an autoincrement default are determined by the data type of the column. Attempting to insert a value outside the range of values supported by the data type raises an exception.

Table 1-2 Maximum and Minimum Values for Columns with Autoincrement Defaults

Data type

Maximum Value

Minimum Value

TINYINT

127 (java.lang.Byte.MAX_VALUE)

-128 (java.lang.Byte.MIN_VALUE)

SMALLINT

32767 (java.lang.Short.MAX_VALUE)

-32768 (java.lang.Short.MIN_VALUE)

INT

2147483647 (java.lang.Integer.MAX_VALUE)

-2147483648 (java.lang.Integer.MIN_VALUE)

LONGINT

9223372036854775807 (java.lang.Long.MAX_VALUE)

-9223372036854775808 (java.lang.Long.MIN_VALUE)

In a non-synchronized database, automatically generated values in a column with an autoincrement default are unique. However, since you can add a default to an existing table, the column can contain values that are not unique. Use a primary key or unique constraint on a column to guarantee uniqueness. Assigning an autoincrement default does not create an index on the column.

Automatically generated values in columns with autoincrement defaults are not guaranteed to be unique in a Cloudscape synchronization system without unique or primary key indexes or constraints defined on them. See the Cloudscape Synchronization Guide for a discussion of the behavior of autoincrement columns and for recommendations on generating unique keys.

An application can determine the value of a column with an autoincrement default for the most recently inserted row in the current connection from the class COM.cloudscape.database.ConnectionInfo (aliased as ConnectionInfo). Use the following SQL-J statement:

VALUES ConnectionInfo.lastAutoincrementValue(
    'schemaName', 'tableName', 'columnName')

The statement returns NULL if no such inserts were made into the column during the current connection.

For example, to find out the value inserted into a column person_id in the table People in the schema APP:

VALUES ConnectionInfo.lastAutoincrementValue(
    'APP', 'PEOPLE', 'PERSON_ID')

NOTE: Specify the schema, table, and column name using the same case as those names are stored in the system tables--that is, all upper case unless you used delimited identifiers when creating those database objects.

Cloudscape keeps track of the last autoincrement value for a column in a cache. It also stores stores the value of what the next autoincrement value will be for the column on disk in the AUTOINCREMENTVALUE column of the SYS.SYSCOLUMNS system table. Rolling back a transaction does not undo this value, and thus rolled-back transactions may leave "gaps" in the values automatically inserted into an autoincrement column. Cloudscape behaves this way to avoid locking a row in SYS.SYSCOLUMNS for the duration of a transaction and keeping concurrency high.

When an insert happens within a trigger action, the value inserted by the trigger action into the autoincrement column is available from ConnectionInfo only within the trigger code. The trigger code is also able to see the value inserted by the statement that caused the trigger to fire. However, the statement that caused the trigger to fire is not able to see the value inserted by the trigger action into the autoincrement column. Likewise, triggers can be nested (or recursive). An SQL statement can cause trigger T1 to fire. T1 in turn executes an SQL statement that causes trigger T2 to fire. If both T1 and T2 insert rows into a table that cause Cloudscape to insert into an autoincrement column, trigger T1 cannot see the autoincrement value caused by T2's insert, but T2 can see the autoincrement value caused by T1's insert. Each nesting level can see autoincrement values generated by itself and previous nesting levels, all the way to the top-level SQL statement that initiated the recursive triggers.

NOTE: When working with import and export on tables with autoincrement defaults, you can preserve the values in the autoincrement default column by dropping the default before importing the data and then re-adding the default when you are done importing. For more information, see the Cloudscape Tools and Utilities Guide.

Cloudscape resets its internal counter of the next autoincrement value for a column (SYS.SYSCOLUMNS.AUTOINCREMENTVALUE) to the initial value defined for the column during bulk insert if insertMode=replace; see Bulk Insert Properties.

NOTE: If Cloudscape is run in a server framework in which connection pooling is used, an application's call to COM.cloudscape.database.ConnectionInfo is not guaranteed to retrieve the correct value for that application.

Overriding Row-Level Locking

The SET LOCKING clause allows you to override row-level locking for the specific table, if your system is set for row-level locking. (If your system is set for table-level locking, you cannot change the locking granularity to row-level locking.) To override row-level locking for the specific table, set locking for the table to TABLE. After you create such a table, you can change locking back to ROW with the a SET LOCKING clause in the ALTER TABLE statement. (For more information about locking granularity, see Lock Granularity.)

CREATE TABLE Examples

CREATE TABLE Cities (
    --define a column-level primary key constraint
    city_id INT CONSTRAINT CITIES_PK PRIMARY KEY,
    -- City is a class alias
    city SERIALIZE(City),
    country_ISO_code CHAR(2)
        CONSTRAINT countries_fk REFERENCES Countries)
-- set the pageSize and pageReservedSpace for the table
PROPERTIES cloudscape.storage.pageSize=8192,
    cloudscape.storage.pageReservedSpace=10
-- turn off row-level locking for this table; it will
-- be read-only once loaded
SET LOCKING = TABLE

CREATE TABLE HotelAvailability
    (hotel_id INT,
    booking_date DATE,
    rooms_taken INT DEFAULT 0,
    -- the table-level primary key definition allows you to
    -- include two columns in the primary key definition
    PRIMARY KEY (hotel_id, booking_date))

-- override default storage properties
-- for a table
CREATE TABLE CustomizedTours(
    group_id INT PRIMARY KEY,
    customized_tour SERIALIZE(Tour))
PROPERTIES cloudscape.storage.pageSize=16384,
    cloudscape.storage.minimumRecordSize=8192

-- assign an autoincrement default to an INTEGER
-- column, and also define a primary key constraint
-- on the column
CREATE TABLE People (
    person_id INT DEFAULT AUTOINCREMENT
        CONSTRAINT people_pk PRIMARY KEY,
    person SERIALIZE(JBMSTours.serializabletypes.Person))

-- assign an autoincrement default to a TINYINT
-- column with an initial value of 5 and an increment value
-- of 5.
CREATE TABLE Groups(
    group_id TINYINT
        DEFAULT AUTOINCREMENT INITIAL 5 INCREMENT 5,
    address VARCHAR(100),
    phone VARCHAR(15))

NOTE: For more examples of CREATE TABLE statements using the various constraints, see CONSTRAINT clause.

Interaction with Java Data Types

You can create columns that store Java objects. For more information, see Java Data Types (User-Defined Data Types).

You can store SQL NULL values in Java data type columns (assuming that the columns do not have NOT NULL constraints). (An SQL NULL value is not the same thing as a Java null reference, although they do map to one another.)

Locking Granularity and Published Tables

When a table is published, it has the same lock granularity at the target as at the source. The SET LOCKING clause of the CREATE TABLE and ALTER TABLE statements is implicitly published to the targets whose publication includes the affected table.

Column Defaults and Published Tables

Column defaults are always implicitly published if a column is published. A column in a target cannot have a different default value than the corresponding column in the source.