UltraLite CREATE TABLE statement

Use this statement to create a table.

Syntax 
CREATE TABLE table-name (
 { column-definition | table-constraint }, ... 
)

column-definition :
column-name  data-type    
[ [ NOT ] NULL ] 
[ DEFAULT column-default ] 
[ column-constraint ]
column-default :
GLOBAL AUTOINCREMENT [ ( number ) ] 
| AUTOINCREMENT 
| CURRENT DATE 
| CURRENT TIME 
| CURRENT TIMESTAMP
| NULL 
| NEWID( )
| constant-value
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 ], ... )
Parameters

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. See Identifiers in UltraLite.

  • data-type   The data type of the column. See Data types in UltraLite.

  • [ 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:

    • 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.

      Tip

      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. See Using GLOBAL AUTOINCREMENT in UltraLite, and UltraLite global_database_id option.

    • [ NOT ] NULL   Controls whether the column can contain NULLs.

    • NEWID( )   A function that generates a unique identifier value. See NEWID function [Miscellaneous].

    • 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. See CURRENT TIMESTAMP special value.

    • CURRENT DATE   Stores the current year, month, and day. See CURRENT DATE special value.

    • CURRENT TIME   Stores the current hour, minute, second and fraction of a second. See CURRENT TIME special value.

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

  • 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   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, provided that inconsistent data is resolved prior to 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 property in effect for the database. To learn the default size, see Accessing UltraLite database properties.

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 schema changes are initiated at the same time. See Schema changes with DDL statements.

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 Dispose method for all data objects (for example, ULDataReader). See Dispose method.

Side effects

Automatic commit.

See also
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 on 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)
);
Copyright © 2009, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.1