Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 17 » UltraLite - Database Management and Developer Guide » UltraLite SQL reference » UltraLite SQL statements

CREATE TABLE statement [UltraLite]

Creates a table.

Syntax
CREATE TABLE [ IF NOT EXISTS ] table-name (
 { column-definition | table-constraint| sync-constraint }, ... 
)
column-definition :
column-name  data-type    
[ [ NOT ] NULL ] 
[ DEFAULT column-default]
[STORE AS FILE (file-name-column) [CASCADE DELETE ] 
[ column-constraint ]
column-default :
AUTOFILENAME(prefix,extension)
| GLOBAL AUTOINCREMENT [ ( number ) ] 
| AUTOINCREMENT 
| CURRENT DATE 
| CURRENT TIME 
| CURRENT TIMESTAMP
| CURRENT UTC TIMESTAMP
| NULL 
| NEWID( )
| constant-value
file-name
"filename"
column-constraint :
PRIMARY KEY
| UNIQUE
table-constraint :
{ [ CONSTRAINT constraint-name ] 
   pkey-constraint 
   | fkey-constraint 
   | unique-key-constraint } 
[ WITH MAX HASH SIZE integer ]
pkey-constraint : 
PRIMARY KEY [ ordered-column-list ]
fkey-constraint : 
[ NOT NULL ] FOREIGN KEY [ role-name ] ( ordered-column-list )
   REFERENCES table-name ( column-name, ... ) 
   [ CHECK ON COMMIT ] 
unique-key-constraint :
UNIQUE ( ordered-column-list )
 ordered-column-list :
( column-name [ ASC | DESC ], ... )
sync-constraint :SYNCHRONIZE {ON| OFF| ALL| DOWNLOAD}
Parameters
  • IF NOT EXISTS clause

    Use this clause to create a table. No changes are made if the named table already exists, and an error is not returned.

  • column-definition

    Defines a column in a table. Available parameters for this clause include:

    • column-name

      The column name is an identifier. Two columns in the same table cannot have the same name.

    • data-type

      The data type of the column.

    • [ NOT ] NULL

      If NOT NULL is specified, or if the column is in a PRIMARY KEY or UNIQUE constraint, the column cannot contain NULL in any row. Otherwise, NULL is allowed.

    • column-default

      Sets the default value for the column. If a DEFAULT value is specified, it is used as the value for the column in any INSERT statement that does not specify a value for the column. If no DEFAULT is specified, it is equivalent to DEFAULT NULL. Default options include:

      • AUTOFILENAME

        This clause supports the storing of external BLOB files in a partitioned UltraLite Java edition database.

        When partitioning the database, the column designated to store the file names requires the AUTOFILENAME(prefix,extension) clause. This clause specifies how new filenames are to be generated for downloaded BLOB values. The prefix and extension values are string literal constants.

      • AUTOINCREMENT

        When using AUTOINCREMENT, the column must be one of the integer data types, or an exact numeric type. On inserts into the table, if a value is not specified for the AUTOINCREMENT column, a unique value larger than any other value in the column is generated. If an INSERT specifies a value for the column that is larger than the current maximum value for the column, that value is used as a starting point for subsequent inserts.

        Note In UltraLite, the autoincrement value is not set to 0 when the table is created, and AUTOINCREMENT generates negative numbers when a signed data type is used for the column. Therefore, declare AUTOINCREMENT columns as unsigned integers to prevent negative values from being used.
      • GLOBAL AUTOINCREMENT

        Similar to AUTOINCREMENT, except that the domain is partitioned. Each partition contains the same number of values. You assign each copy of the database a unique global database identification number. UltraLite supplies default values in a database only from the partition uniquely identified by that database's number.

        Note If the column is of type BIGINT or UNSIGNED BIGINT, the default partition size is 2^32 = 4294967296; for columns of all other types, the default partition size is 2^16 = 65536. Since these defaults may be inappropriate, especially if your column is not of type INT or BIGINT, it is best to specify the partition size explicitly.
      • [ NOT ] NULL

        Controls whether the column can contain NULLs.

      • NEWID( )

        A function that generates a unique identifier value.

      • CURRENT TIMESTAMP

        Combines CURRENT DATE and CURRENT TIME to form a TIMESTAMP value containing the year, month, day, hour, minute, second, and fraction of a second. The fraction of a second is stored to 3 decimal places. The accuracy is limited by the accuracy of the system clock.

      • CURRENT UTC TIMESTAMP

        A TIMESTAMP WITH TIME ZONE value containing the Coordinated Universal Time (UTC) comprised of the year, month, day, hour, minute, second, fraction of a second, and time zone. The fraction of a second is stored to 3 decimal places. The accuracy is limited by the accuracy of the system clock.

      • CURRENT DATE

        Stores the current year, month, and day.

      • CURRENT TIME

        Stores the current hour, minute, second and fraction of a second.

      • constant-value

        A constant for the data type of the column. Typically the constant is a number or a string.

    • STORE AS FILE (file-name-column) [CASCADE DELETE]

      Supported by UltraLite Java edition databases only.

      Specify that a LONG BINARY column is to be stored externally (partitioning the database) and specify the file-name-column to name the column that will be used to store the file names of the externally stored BLOB values. A column with this clause must be of type LONG BINARY and behave as a read-only column.

    • column-constraint clause

      Specify a column constraint to restrict the values allowed in a column. A column constraint can be one of:

      • PRIMARY KEY

        When set as part of a column-constraint, the PRIMARY KEY clause sets the column as the primary key for the table. Primary keys uniquely identify each row in a table. By default, columns included in primary keys do not allow NULL.

      • UNIQUE

        Identifies one or more columns that uniquely identify each row in the table. No two rows in the table can have the same values in all the named column(s). A table may have more than one unique constraint. NULL values are not allowed.

  • table-constraint clause

    Specify a table constraint to restrict the values that one or more columns in the table can hold. Use the CONSTRAINT clause to specify an identifier for the table constraint. Table constraints can be in the form of a primary key constraint, a foreign key constraint, or a unique constraint, as defined below:

    • pkey-constraint clause

      Sets the specified column(s) as the primary key for the table. Primary keys uniquely identify each row in a table. Columns included in primary keys cannot allow NULLs.

    • fkey-constraint clause

      Specify a foreign key constraint to restrict values for one or more columns that must match the values in a primary key (or a unique constraint) of another table.

      • NOT NULL clause

        Specify NOT NULL to disallow NULLs in the foreign key columns. A NULL in a foreign key means that no row in the primary table corresponds to this row in the foreign table. If at least one value in a multi-column foreign key is NULL, there is no restriction on the values that can be held in other columns of the key.

      • role-name clause

        Specify a role-name to name the foreign key. role-name is used to distinguish foreign keys within the same table. Alternatively, you can name the foreign key using CONSTRAINT constraint-name. However, do not use both methods to name a foreign key.

      • REFERENCES clause

        Specify the REFERENCES clause to define one or more columns in the primary table to use as the foreign key constraint. Any column-name you specify in a REFERENCES column constraint must be a column in the primary table, and must be subject to a unique constraint or primary key constraint.

      • CHECK ON COMMIT

        not supported by UltraLite Java edition databases. Specify CHECK ON COMMIT to cause the database server to wait for a COMMIT before enforcing foreign key constraints. By default, foreign key constraints are enforced immediately during insert, update, or delete operations. However, when CHECK ON COMMIT is set, database changes can be made in any order, even if they violate foreign key constraints, if inconsistent data is resolved before the next COMMIT.

    • unique-key-constraint clause

      Specify a unique constraint to identify one or more columns that uniquely identify each row in the table. No two rows in the table can have the same values in all the named column(s). A table may have more than one unique constraint.

    • WITH MAX HASH SIZE

      Sets the hash size (in bytes) for this index. This value overrides the default MaxHashSize database property in effect for the database.

  • sync-constraint clause

    Specify a sync constraint to determine whether a table can be synchronized or not and whether all rows are uploaded, just changes to the table are uploaded, or no changes to the table are uploaded.

    • SYNCHRONIZE ON

      Default setting - the table can be synchronized and only changes to the table are sent in the upload.

    • SYNCHRONIZE OFF

      The table cannot be synchronized and it is an error to include the table in a publication.

    • SYNCHRONIZE ALL

      The table can be synchronized and all rows in the table are sent in the upload. This constraint is not supported by UltraLite Java edition databases.

    • SYNCHRONIZE DOWNLOAD

      The table can be synchronized with changes to the consolidated database but no local changes are uploaded.

Remarks

Column constraints are normally used unless the constraint references more than one column in the table. In these cases, a table constraint must be used. If a statement causes a violation of a constraint, execution of the statement does not complete. Any changes made by the statement before error detection are undone, and an error is reported.

Each row in the table has a unique primary key value.

If no role name is specified, the role name is assigned as follows:

  1. If there is no foreign key with a role name the same as the table name, the table name is assigned as the role name.

  2. If the table name is already taken, the role name is the table name concatenated with a zero-padded, three-digit number unique to the table.

  • Schema changes

    Statements are not released if database schema changes are initiated at the same time.

    UltraLite does not process requests or queries referencing the table while the CREATE TABLE statement is being processed. Furthermore, you cannot execute CREATE TABLE when the database includes active queries or uncommitted transactions.

    For UltraLite.NET users: You cannot execute this statement unless you also call the ULBulkCopy.Dispose method for all data objects (for example, ULDataReader).

  • Synchronization of external BLOB columns (UltraLiteJ only)

    On the consolidated database, the filename column is stored as a regular CHAR column and the BLOB file column is stored as a regular BLOB (LONG BINARY) column. On a download, the filename column is ignored and a new filename is generated using the database option (Connection.OPTION_BLOB_FILE_BASE_DIR) and the prefix and extension strings specified on the DEFAULT AUTOFILENAME clause. For J2SE value stored in the filename column will match the syntax <database_option_blob_file_base_dir><prefix><auto generated integer value>.<extension>.

  • Accessing external BLOB columns (UltraLiteJ only)

    Files containing external BLOB values are only opened when the client application attempts to read the column value. At that time, the filename stored in the column designated by the STORE AS FILE clause must be a valid filename. If UltraLite determines that a filename does not begin with the prefix "file://", it will prepend the filename with the value of the OPTION_BLOB_FILE_BASE_DIR option before attempting to open it.

    A BLOB file is inserted into the database by specifying a filename for the file-name-column. The inserted filename must be a valid filename. On insert, a value for the STORE AS FILE column may not be specified.

    Once a file is inserted into the database, the database assumes full control over the file and assumes that no outside modification is made.

Side effects

Automatic commit.

Example

The following statement creates a table for a library database to hold book information.

CREATE TABLE library_books (
   isbn CHAR(20)      PRIMARY KEY,
   copyright_date     DATE,
   title              CHAR(100),
   author             CHAR(50),
   location           CHAR(50),
   FOREIGN KEY location REFERENCES room
)

The following statement creates a table for a library database to hold information about borrowed books. The default value for date_borrowed indicates that the book is borrowed on the day the entry is made. The date_returned column is NULL until the book is returned.

CREATE TABLE borrowed_book (
   loaner_name   CHAR(100) PRIMARY KEY,
   date_borrowed DATE NOT NULL DEFAULT CURRENT DATE,
   date_returned DATE,
   book CHAR(20),
   FOREIGN KEY (book) REFERENCES library_books (isbn)
)

The following statement creates tables for a sales database to hold order and order item information.

CREATE TABLE Orders (
   order_num INTEGER NOT NULL PRIMARY KEY,
   date_ordered DATE,
   name CHAR(80)
);
CREATE TABLE Order_item (
   order_num        INTEGER NOT NULL,
   item_num         SMALLINT NOT NULL,
   PRIMARY KEY (order_num, item_num),
   FOREIGN KEY (order_num)
   REFERENCES Orders (order_num)
)