Use this statement to create a table.
SyntaxCREATE 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) [CASCADE DELETE ] [ column-constraint ]
column-default : AUTOFILENAME(prefix,extension) | GLOBAL AUTOINCREMENT [ ( number ) ] | AUTOINCREMENT | CURRENT DATE | CURRENT TIME | CURRENT 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
ParametersIF 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. See Identifiers in UltraLite.
UltraLiteJ, using BlackBerry OS 4.2 or J2SE, supports the partitioning of database files such that external files may now be used to store large BLOB values, with the files referenced using the file_name and file_contents columns. The file_name column stores a data type of CHAR(size) and the file_contents column stores a LONG BINARY data type. This column is read-only.
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:
AUTOFILENAME This clause supports the storing of external BLOB files in a partitioned UltraLiteJ database.
The file_name column 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. The filename must be a valid filename. For the BlackBerry, relative filenames are resolved against the database option OPTION_BLOB_FILE_BASE_DIR. 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. See OPTION_BLOB_FILE_BASE_DIR variable.
AUTOINCREMENT UltraLiteJ only.
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.
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.
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.
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.
STORE AS FILE (file-name) [CASCADE DELETE] UltraLiteJ only.
The file_contents column must specify an existing CHAR column as the STORE AS FILE argument. The file_contents column behaves as a read-only column.
On deletion of a row containing a BLOB file, the file will be deleted if CASCADE DELETE is specified. Otherwise the file will be left in the file system and will no longer be a property of the database. Specifying the CASCADE DELETE clause ensures that a file is never inserted into the database twice. On rollback of an insert, the file will not be deleted even if CASCADE DELETE is specified.
An update that changes the contents of a filename column is identical to a DELETE followed by an INSERT. If CASCADE DELETE is specified, the file pointed to by the old filename will be deleted. If a row with a BLOB file is updated and the filename column is not changed, or if the filename column is updated to be the exact same value, the file is protected from being deleted. A BLOB file column may not be changed in an UPDATE statement.
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 UltraLite only. Not supported for UltraLiteJ. 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 property in effect for the database. To learn the default size, see Accessing UltraLite database properties. This is not supported for UltraLiteJ.
sync-constraint clause Specify a sync constraint to determine whether a table can be synchronized or not.
SYNCHRONIZE Determines whether a table can be synchronized or not and whether all rows are uploaded or just changes to the table are uploaded. Valid values are ON (default setting - the table can be synchronized and only changes to the table are sent in the upload), OFF (the table cannot be synchronized and it is an error to include the table in a publication), and ALL (used for UltraLite only - the table can be synchronized and all rows in the table are sent in the upload).
RemarksColumn 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:
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.
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.
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 the syntax is <database_option_blob_file_base_dir><prefix><auto generated integer value>.<extension> and for the BlackBerry the syntax is
<prefix><auto generated integer value>.<extension>. Therefore, for the BlackBerry generated filenames are always relative.
Side effectsAutomatic commit.
See also ExampleThe 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) ); |
 |
Discuss this page in DocCommentXchange.
|
Copyright © 2010, iAnywhere Solutions, Inc. - SQL Anywhere 12.0.0 |