|
Create a new table in the database.
CREATE TABLE table-name
( { column-definition | table-constraint }, ... )
column-definition :
column-name data-type [ [ NOT ]
NULL ] [ DEFAULT column-default ] [ column-constraint ... ]
column-constraint :
[ UNIQUE | PRIMARY KEY ]
table-constraint :
[ CONSTRAINT constraint-name ] spec
column-default :
{GLOBAL AUTOINCREMENT [ ( number ) ] |
AUTOINCREMENT | CURRENT DATE |
CURRENT TIME | CURRENT TIMESTAMP |
NULL |
NEWID( ) |
constant-value}
spec :
{ PRIMARY KEY ( ordered-column-list |
[ NOT NULL ] FOREIGN KEY [ role-name ]( ordered-column-list )
REFERENCES table-name ( column-name, . . . )
[ CHECK ON COMMIT ] |
UNIQUE ( ordered-column-list ) } [ WITH MAX HASH SIZE number ]
ordered-column-list :
[ ( column-name [ ASC | DESC ] ...,] ) ]
column-definition Define a column in the 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 UNIQUE or PRIMARY KEY constraint, the column cannot contain NULL in any row. Otherwise, NULL is allowed.
column-default and column-constraint Clauses that contain several parameters that are used to create an expression. See Expressions in UltraLite.
column-constraint A short form for the equivalent table constraint, with only the current row. A column constraint can be one of:
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.
PRIMARY KEY This is the same as a unique constraint, except that a column can have only one primary key constraint. The primary key usually identifies the best identifier for a row. For example, the customer number might be the primary key for the customer table.
Columns included in primary keys are automatically made NOT NULL.
table-constraint A table constraint restricts the values that one or more columns in the table can hold in order to help ensure the integrity of data in the database. If a statement would cause a violation of a constraint, execution of the statement does not complete, any changes made by the statement before error detection are rolled back, and an error is reported.
column-default 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.
TipIn 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. You should 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. See Using GLOBAL AUTOINCREMENT in UltraLite.
Each partition contains the same number of values. You assign each copy of the database a unique global database identification number. See UltraLite global_database_id option. UltraLite supplies default values in a database only from the partition uniquely identified by that database's number.NULL The column can contain NULL.
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 CURRENT DATE returns the current year, month, and day. See CURRENT DATE special value.
CURRENT TIME 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.
spec Describes additional specifications. spec can be one of:
Primary key The primary key usually identifies the collection of columns to immediately identify the rows in a table. Columns included in primary keys cannot allow NULL.
Foreign key Restricts the values for a set of columns to match the values in a primary key or, less commonly, a unique constraint of another table (the primary table).
role-name The role name is the name of the foreign key. The main function of the role-name is to distinguish two foreign keys to the same table. Alternatively, you can name the foreign key with CONSTRAINT constraint-name. However, do not use both methods to name a foreign key.
NOT NULL Disallow NULL 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.
CHECK ON COMMIT Causes the database server to wait for a COMMIT before checking that foreign keys are enforced.
This means that database changes can be applied in any order. Otherwise, the primary key (or values for UNIQUE constraint) must be in the database before a row with those foreign key values can be added.
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.
ordered-column-list An ordered list of columns. The ordered list can be sorted in ascending or descending order.
WITH MAX HASH SIZE Sets the default index hash size in bytes. If you do not set this value, a default size of 8 bytes is used for index hashing. See UltraLite max_hash_size property.
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 would cause 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.
Automatic commit.
The following example 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 example 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 example 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) )