![]() |
SQL-J Language Reference
|
Reference Manual |
CREATE TABLE statementA 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
SimpleColumnName DataType AutoincrementDefaultDefinition
AUTOINCREMENT [ INITIAL 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: NEW: The DEFAULT type of AUTOINCREMENT is new in Version 3.6. Column DefaultsFor 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 DefaultsFor 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. 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( 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( 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 LockingThe 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 (
CREATE TABLE HotelAvailability
-- override default storage properties
-- assign an autoincrement default to an INTEGER
-- assign an autoincrement default to a TINYINT NOTE: For more examples of CREATE TABLE statements using the various constraints, see CONSTRAINT clause. Interaction with Java Data TypesYou 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 TablesWhen 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 TablesColumn 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. |
|
![]() Cloudscape Version 3.6 For information about Cloudscape technical support, go to: www.cloudscape.com/support/.Copyright © 1998, 1999, 2000 Informix Software, Inc. All rights reserved. |