CREATE TABLE

Description

Creates a table definition. A table definition consists of a list of column definitions that make up a table row. c-treeACE 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 [ owner_name. ] table_name

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

  [ STORAGE_ATTRIBUTES 'attributes' ]

  ;

CREATE TABLE [ 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 } ]

  [ column_constraint [ column_constraint … ] ]

Arguments

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. c-treeACE 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:

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'

A quoted string that specifies specific c-treeACE SQL table attributes. To combine STORAGE_ATTRIBUTE options, separate them with a semicolon (;) as shown here:

CREATE TABLE small_preimage_table (name CHAR(10), age SMALLINT) STORAGE_ATTRIBUTES 'ENCR=TWF24;PREIMG' 

c-treeACE SQL supports the following STORAGE_ATTRIBUTES parameters:

• ‘CAMO’ - A lightweight, high performance encryption option with little overhead of server processing. This encryption option gives a reasonable level of protection from casual inspection of the c-tree data and index files.
• ‘ENCR=crypt’ - Advanced encryption options providing the highest level of security and protection for your data. Options include AES (Rijndael), Twofish, Blowfish, and DES. crypt can be one of the following:
  • 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 c-treeACE 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 c-treeACE SQL tables that are not required to be recovered in the event of catastrophic failure, yet retain atomicity of transactions.
• ‘HUGE’ - (Default) Denotes that a table should be created as a c-tree HUGE file (64-bit file offset addressing).
• ‘NOTHUGE’ - 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 c-treeACE SQL Server configuration option to reverse this behavior.

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)

          AS

          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.