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-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}
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.
UltraLite Java edition databases support the partitioning of database files such that external files may now be used to store large BLOB values, with the files referenced by two columns: the first stores the file name and should have a data type of CHAR(size) ... AUTOFILENAME(...), and the second is used to access the file contents and is declared as a LONG BINARY STORE AS ... data type. The file contents 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 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. See Connection.OPTION_BLOB_FILE_BASE_DIR variable [BlackBerry] [UltraLiteJ].
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.
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 UTC TIMESTAMP A TIMESTAMP WITH TIMEZONE 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. See CURRENT UTC 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-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 property in effect for the database. This clause is not supported for UltraLite Java edition databases. To learn the default size, see Reading database properties. Also see UltraLite max_hash_size creation parameter.
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.
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 UltraLite database schemas.
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 ULBulkCopy.Dispose method [UltraLite.NET].
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> and for the BlackBerry the value stored will match the syntax <prefix><auto generated integer value>.<extension>. Therefore, for the BlackBerry generated filenames are always relative.
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. 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.
A BLOB file is inserted into the database by specifying a filename for the file-name-column column. The inserted filename must be a valid filename and, for the Blackberry, it must follow the format for a fully qualified, absolute path file name as described in the file URL format in IETF RFCs 1738 & 2396 (see the Package javax.microedition.io.file Description of BlackBerry JDE API documentation). 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 will be made.
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 © 2012, iAnywhere Solutions, Inc. - SQL Anywhere 12.0.1 |