Creates a new table in the database and, optionally, to create a table on a remote server.
SyntaxCREATE [ GLOBAL TEMPORARY ] TABLE [ IF NOT EXISTS ] [ owner.]table-name ( { column-definition | table-constraint | pctfree }, ... ) [ { IN | ON } dbspace-name ] [ ENCRYPTED ] [ ON COMMIT { DELETE | PRESERVE } ROWS | NOT TRANSACTIONAL ] [ AT location-string ] [ SHARE BY ALL ]
column-definition : column-name data-type [ COMPRESSED ] [ INLINE { inline-length | USE DEFAULT } ] [ PREFIX { prefix-length | USE DEFAULT } ] [ [ NO ] INDEX ] [ [ NOT ] NULL ] [ DEFAULT default-value | IDENTITY ] [ column-constraint ... ]
default-value : special-value | string | global-variable | [ - ] number | ( constant-expression ) | ( sequence-expression ) | built-in-function( constant-expression ) | AUTOINCREMENT | GLOBAL AUTOINCREMENT [ ( partition-size ) ]
special-value : CURRENT DATABASE | CURRENT DATE | CURRENT TIME | [ CURRENT ] TIMESTAMP | CURRENT PUBLISHER | CURRENT REMOTE USER | [ CURRENT ] USER | [ CURRENT ] UTC TIMESTAMP | LAST USER | NULL
column-constraint : [ CONSTRAINT constraint-name ] { UNIQUE [ CLUSTERED ] | PRIMARY KEY [ CLUSTERED ] [ ASC | DESC ] | REFERENCES table-name [ ( column-name ) ] [ MATCH [ UNIQUE ] { SIMPLE | FULL } ] [ action-list ] [ CLUSTERED ] | CHECK ( condition ) } | COMPUTE ( expression )
table-constraint : [ CONSTRAINT constraint-name ] { UNIQUE [ CLUSTERED ] ( column-name [ ASC | DESC ], ... ) | PRIMARY KEY [ CLUSTERED ] ( column-name [ ASC | DESC ], ... ) | CHECK ( condition ) | foreign-key-constraint }
foreign-key-constraint : [ NOT NULL ] FOREIGN KEY [ role-name ] [ ( column-name [ ASC | DESC ], ... ) ] REFERENCES table-name [ ( column-name, ... ) ] [ MATCH [ UNIQUE] { SIMPLE | FULL } ] [ action-list ] [ CHECK ON COMMIT ] [ CLUSTERED ] [ FOR OLAP WORKLOAD ]
action-list : [ ON UPDATE action ] [ ON DELETE action ]
action : CASCADE | SET NULL | SET DEFAULT | RESTRICT
location-string : remote-server-name.[db-name].[owner].object-name | remote-server-name;[db-name];[owner];object-name
pctfree : PCTFREE percent-free-spacepercent-free-space : integer
ParametersIN clause Use this clause to specify the dbspace in which the base table is located. If this clause is not specified, then the base table is created in the dbspace specified by the default_dbspace option.
Temporary tables can only be created in the temporary dbspace. If you are creating a GLOBAL TEMPORARY table, and specify IN, the table is created in the temporary dbspace. If you specify a user-defined dbspace, an error is returned.
ENCRYPTED clause The encrypted clause specifies that the table should be encrypted. You must enable table encryption when you create a database if you want to encrypt tables. The table is encrypted using the encryption key and algorithm specified at database creation time.
ON COMMIT clause The ON COMMIT clause is allowed only for temporary tables. By default, the rows of a temporary table are deleted on COMMIT. If the SHARE BY ALL clause is specified, either ON COMMIT PRESERVE ROWS or NOT TRANSACTIONAL must be specified.
NOT TRANSACTIONAL clause The NOT TRANSACTIONAL clause is allowed when creating a global temporary table. A table created using NOT TRANSACTIONAL is not affected by either COMMIT or ROLLBACK. If the SHARE BY ALL clause is specified, either ON COMMIT PRESERVE ROWS or NOT TRANSACTIONAL must be specified.
AT clause Create a remote table on a different server specified by location-string, and a proxy table on the current database that maps to the remote table. The AT clause supports the semicolon (;) as a field delimiter in location-string. If no semicolon is present, a period is the field delimiter. This syntax allows file names and extensions to be used in the database and owner fields.
For example, the following statement maps the table a1 to the Microsoft Access file mydbfile.mdb:
CREATE TABLE a1 AT 'access;d:\mydbfile.mdb;;a1'; |
Windows Mobile does not support the AT clause.
Foreign key definitions are ignored on remote tables. Foreign key definitions on local tables that refer to remote tables are also ignored. Primary key definitions are sent to the remote server if the database server supports primary keys.
SHARE BY ALL clause Use this clause only when creating global temporary tables to allow the table to be shared by all connections to the database. If the SHARE BY ALL clause is specified, either ON COMMIT PRESERVE ROWS or NOT TRANSACTIONAL must be specified.
IF NOT EXISTS clause No changes are made if the named table already exists, and an error is not returned.
column-definition Define a column in the table. The following are part of column definitions.
column-name The column name is an identifier. Two columns in the same table cannot have the same name.
data-type The type of data stored in the column.
COMPRESSED clause Compress the column. For example, the following statement creates a table, t, with two columns: filename and contents. The contents column is LONG BINARY and is compressed:
CREATE TABLE t ( filename VARCHAR(255), contents LONG BINARY COMPRESSED ); |
INLINE and PREFIX clauses The INLINE clause specifies the maximum BLOB size, in bytes, to store within the row. BLOBs smaller than or equal to the value specified by the INLINE clause are stored within the row. BLOBs that exceed the value specified by the INLINE clause are stored outside the row in table extension pages. Also, a copy of some bytes from the beginning of the BLOB may be kept in the row when a BLOB is larger than the INLINE value. Use the PREFIX clause to specify how many bytes are kept in the row. The PREFIX clause can improve the performance of requests that need the prefix bytes of a BLOB to determine if a row is accepted or rejected.
The prefix data for a compressed column is stored uncompressed, so if all the data required to satisfy a request is stored in the prefix, no decompression is necessary.
If neither INLINE nor PREFIX is specified, or if USE DEFAULT is specified, default values are applied as follows:
For character data type columns, such as CHAR, NCHAR, and LONG VARCHAR, the default value of INLINE is 256, and the default value of PREFIX is 8.
For binary data type columns, such as BINARY, LONG BINARY, VARBINARY, BIT, VARBIT, LONG VARBIT, BIT VARYING, and UUID, the default value of INLINE is 256, and the default value of PREFIX is 0.
It is strongly recommended that you use the default values unless there are specific circumstances that require a different setting. The default values have been chosen to balance performance and disk space requirements. For example, if you set INLINE to a large value, and all the BLOBs are stored inline, row processing performance may degrade. If you set PREFIX too high, you increase the amount of disk space required to store BLOBs since the prefix data is a duplicate of a portion of the BLOB.
If only one of the values is specified, the other value is automatically set to the largest amount that does not conflict with the specified value. Neither the INLINE nor PREFIX value can exceed the database page size. Also, there is a small amount of overhead reserved in a table page that cannot be used to store row data. Therefore, specifying an INLINE value approximate to the database page size can result in a slightly smaller number of bytes being stored inline.
INDEX and NO INDEX clauses When storing BLOBs (character or binary types only), specify INDEX to create BLOB indexes on inserted values that exceed the internal BLOB size threshold (approximately eight database pages). This is the default behavior.
BLOB indexes can improve performance when random access searches within the BLOBs are required. However, for some types of BLOB values, such as images and multimedia files that will never require random-access, performance can improve if BLOB indexing is turned off. To turn off BLOB indexing for a column, specify NO INDEX.
A BLOB index is not the same as a table index. A table index is created to index values in one or more columns.
NULL and NOT NULL clauses If NULL is specified, NULL values are allowed in the column. This is the default behavior. This setting is controlled by the allow_nulls_by_default database option.
By default, columns declared as BIT are not nullable, but they can be explicitly made nullable.
If NOT NULL is specified, NULL values are not allowed.
If the column is part of a UNIQUE or PRIMARY KEY constraint, the column cannot contain NULL, even if NULL is specified.
DEFAULT clause 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 value is specified, it is equivalent to DEFAULT NULL.
Following is a list of possible values for DEFAULT:
special-value You use one of several specials values in the DEFAULT clause.
[ CURRENT ] TIMESTAMP Provides a way of indicating when each row in the table was last modified. When a column is declared with DEFAULT TIMESTAMP, a default value is provided for inserts, and the value is updated with the current date and time of day whenever the row is updated.
To provide a default value on insert, but not update the column whenever the row is updated, use DEFAULT CURRENT TIMESTAMP instead of DEFAULT TIMESTAMP.
Columns declared with DEFAULT TIMESTAMP contain unique values, so that applications can detect near-simultaneous updates to the same row. If the current TIMESTAMP value is the same as the last value, it is incremented by the value of the default_timestamp_increment option.
You can automatically truncate TIMESTAMP values in SQL Anywhere based on the default_timestamp_increment option. This is useful for maintaining compatibility with other database software that records less precise timestamp values.
The global variable @@dbts returns a TIMESTAMP value representing the last value generated for a column using DEFAULT TIMESTAMP.
[ CURRENT ] UTC TIMESTAMP Provides a way of indicating when each row in the table was last modified. When a column is declared with DEFAULT UTC TIMESTAMP, a default value is provided for inserts, and the value is updated with the current Coordinated Universal Time (UTC) whenever the row is updated.
To provide a default value on insert, but not update the column whenever the row is updated, use DEFAULT CURRENT UTC TIMESTAMP instead of DEFAULT UTC TIMESTAMP.
The behavior of this default is the same as TIMESTAMP and CURRENT TIMESTAMP except that the date and time of day is in Coordinated Universal Time (UTC).
string See Strings.
global-variable See Global variables.
constant-expression Constant expressions that do not reference database objects are allowed in a DEFAULT clause, so functions such as GETDATE or DATEADD can be used. If the expression is not a function or simple value, it must be enclosed in parentheses.
sequence-expression You can set DEFAULT to the current value or next value from a sequence in the database.
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 inserted and then used as a starting point for subsequent inserts.
Deleting rows does not decrement the AUTOINCREMENT counter. Gaps created by deleting rows can only be filled by explicit assignment when using an insert. After an explicit insert of a column value less than the maximum, subsequent rows without explicit assignment are still automatically incremented with a value of one greater than the previous maximum.
You can find the most recently inserted value of the column by inspecting the @@identity global variable.
AUTOINCREMENT values are maintained as signed 64-bit integers, corresponding to the data type of the max_identity column in the SYSTABCOL system view. When the next value to be generated exceeds the maximum value that can be stored in the column to which the AUTOINCREMENT is assigned, NULL is returned. If the column has been declared to not allow NULLs, as is true for primary key columns, a SQL error is generated.
The next value to use for a column can be reset using the sa_reset_identity procedure.
GLOBAL AUTOINCREMENT This default is intended for use when multiple databases are used in a MobiLink synchronization environment or SQL Remote replication.
This option is 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. SQL Anywhere supplies default values in a database only from the partition uniquely identified by that database's number.
The partition size can be specified in parentheses immediately following the AUTOINCREMENT keyword. The partition size can be any positive integer, although the partition size is generally chosen so that the supply of numbers within any one partition will rarely, if ever, be exhausted.
If the column is of type BIGINT or UNSIGNED BIGINT, the default partition size is 232 = 4294967296; for columns of all other types, the default partition size is 216 = 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.
When using this default, the value of the public option global_database_id in each database must be set to a unique, non-negative integer. This value uniquely identifies the database and indicates from which partition default values are to be assigned. The range of allowed values is np + 1 to p(n + 1), where n is the value of the public option global_database_id and p is the partition size. For example, if you define the partition size to be 1000 and set global_database_id to 3, then the range is from 3001 to 4000.
If the previous value is less than p(n + 1), the next default value is one greater than the previous largest value in the column. If the column contains no values, the first default value is np + 1. Default column values are not affected by values in the column outside the current partition; that is, by numbers less than np + 1 or greater than p(n + 1). Such values may be present if they have been replicated from another database via MobiLink or SQL Remote.
You can find the most recently inserted value of the column by inspecting the @@identity global variable.
GLOBAL AUTOINCREMENT values are maintained as signed 64-bit integers, corresponding to the data type of the max_identity column in the SYSTABCOL system view. When the supply of values within the partition has been exhausted, NULL is returned. If the column has been declared to not allow NULLs, as is true for primary key columns, a SQL error is generated. In this case, a new value of global_database_id should be assigned to the database to allow default values to be chosen from another partition. To detect that the supply of unused values is low and handle this condition, create an event of type GlobalAutoincrement.
Because the public option global_database_id cannot be set to a negative value, the values chosen are always positive. The maximum identification number is restricted only by the column data type and the partition size.
If the public option global_database_id is set to the default value of 2147483647, a NULL value is inserted into the column. If NULL values are not permitted, attempting to insert the row causes an error.
The next value to use for a column can be reset using the sa_reset_identity procedure.
LAST USER LAST USER is the user ID of the user who last modified the row.
LAST USER can be used as a default value in columns with character data types.
On INSERT, this default has the same effect as CURRENT USER.
On UPDATE, if a column with a default of LAST USER is not explicitly modified, it is changed to the name of the current user.
When used in conjunction with DEFAULT TIMESTAMP or DEFAULT UTC TIMESTAMP, a default of LAST USER can be used to record (in separate columns) both the user and the date and time a row was last changed.
IDENTITY clause IDENTITY is a Transact-SQL-compatible alternative to using DEFAULT AUTOINCREMENT. In SQL Anywhere, a column defined with IDENTITY is implemented as DEFAULT AUTOINCREMENT.
column-constraint and table-constraint clauses Column and table constraints 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 undone, and an error is reported. There are two classes of constraints that can be created: check constraint, and referential integrity (RI) constraints. Check constraints are used to specify conditions that must be satisfied by values of columns being put into the database. RI constraints establish a relationship between data in different tables that must be maintained in addition to specifying uniqueness requirements for data.
There are three types of RI constraints: primary key, foreign key, and unique constraint. When you create an RI constraint (primary key, foreign key or unique constraint), the database server enforces the constraint by implicitly creating an index on the columns that make up the key of the constraint. The index is created on the key for the constraint as specified. A key consists of an ordered list of columns and a sequencing of values (ASC/DESC) for each column.
Constraints can be specified on columns or tables. A column constraint refers to one column in a table, while a table constraint can refer to one or more columns in a table.
PRIMARY KEY clause A primary key uniquely defines each row in the table. Primary keys comprise one or more columns. A table cannot have more than one primary key. In a column-constraint clause, specifying PRIMARY KEY indicates that the column is the primary key for the table. In a table-constraint, you use the PRIMARY KEY clause to specify one or more columns that, when combined in the specified order, make up the primary key for the table.
The ordering of columns in a primary key need not match the respective ordinal numbers of the columns. That is, the columns in a primary key need not have the same physical order in the row. Additionally, you cannot specify duplicate column names.
When you create a primary key, an index for the key is automatically created. You can specify the sequencing of values in the index by specifying ASC (ascending) or DESC (descending) for each column. You can also specify whether to cluster the index, using the CLUSTERED keyword.
Columns included in primary keys cannot allow NULL. Each row in the table has a unique primary key value.
It is recommended that you do not use approximate data types such as FLOAT and DOUBLE for primary keys. Approximate numeric data types are subject to rounding errors after arithmetic operations.
Spatial columns cannot be included in a primary key.
FOREIGN KEY clause A foreign key restricts the values for a set of columns to match the values in a primary key or a unique constraint of another table (the primary table). For example, a foreign key constraint could be used to ensure that a customer number in an invoice table corresponds to a customer number in the Customers table.
The foreign key column order does not need to reflect the order of columns in the table.
Duplicate column names are not allowed in the foreign key specification.
The default action is RESTRICT if no action is specified for an UPDATE or DELETE operation.
When you create a foreign key, an index for the key is automatically created. You can specify the sequencing of values in the index by specifying ASC (ascending) or DESC (descending) for each column. You can also specify whether to cluster the index, using the CLUSTERED keyword.
A global temporary table cannot have a foreign key that references a base table and a base table cannot have a foreign key that references a global temporary table.
NOT NULL clause 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.
role-name clause The role name is the name of the foreign key. The main function of the role name is to distinguish between two foreign keys to the same table. 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.
REFERENCES clause A foreign key constraint can be implemented using a REFERENCES column constraint (single column only) or a FOREIGN KEY table constraint, in which case the constraint can specify one or more columns. If you specify column-name in a REFERENCES column constraint, it must be a column in the primary table, must be subject to a unique constraint or primary key constraint, and that constraint must consist of only that one column. If you do not specify column-name, the foreign key column references the single primary key column of the primary table.
MATCH clause The MATCH clause determines what is considered a match when using a multi-column foreign key by allowing you to regulate what constitutes an orphaned row versus what constitutes a violation of referential integrity. The MATCH clause also allows you to specify uniqueness for the key, thereby eliminating the need to declare uniqueness separately.
The following is a list of MATCH types you can specify. See the Examples section at the end of this topic for a description of how the MATCH type affects matching behavior.
MATCH [ UNIQUE ] SIMPLE A match occurs for a row in the foreign key table if all the column values match the corresponding column values present in a row of the primary key table. A row is orphaned in the foreign key table if at least one column value in the foreign key is NULL.
MATCH SIMPLE is the default behavior.
If the UNIQUE keyword is specified, the referencing table can have only one match for non-NULL key values.
MATCH [ UNIQUE ] FULL A match occurs for a row in the foreign key table if none of the values are NULL and the values match the corresponding column values in a row of the primary key table. A row is orphaned if all column values in the foreign key are NULL.
If the UNIQUE keyword is specified, the referencing table can have only one match for non-NULL key values.
UNIQUE clause In a column-constraint clause, a UNIQUE constraint specifies that the values in the column must be unique. In a table-constraint clause, the UNIQUE constraint 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 can have more than one UNIQUE constraint.
A UNIQUE constraint is not the same as a unique index. Columns of a unique index are allowed to be NULL, while columns in a UNIQUE constraint are not. Also, a foreign key can reference either a primary key or a UNIQUE constraint, but cannot reference a unique index since a unique index can include multiple instances of NULL.
Columns in a UNIQUE constraint can be specified in any order. Additionally, you can specify the sequencing of values in the corresponding index that is automatically created, by specifying ASC (ascending) or DESC (descending) for each column. You cannot specify duplicate column names, however.
It is recommended that you do not use approximate data types such as FLOAT and DOUBLE for columns with unique constraints. Approximate numeric data types are subject to rounding errors after arithmetic operations.
You can also specify whether to cluster the constraint, using the CLUSTERED keyword.
CHECK clause This constraint allows arbitrary conditions to be verified. For example, a CHECK constraint could be used to ensure that a column called Sex only contains the values M or F.
If you need to create a CHECK constraint that involves a relationship between two or more columns in the table (for example, column A must be less than column B), define a table constraint instead.
No row in a table is allowed to violate a CHECK constraint. If an INSERT or UPDATE statement would cause a row to violate the constraint, the operation is not permitted and the effects of the statement are undone. The change is rejected only if a CHECK constraint condition evaluates to FALSE, and the change is allowed if a CHECK constraint condition evaluates to TRUE or UNKNOWN.
COMPUTE clause The COMPUTE clause is only for use in a column-constraint clause.
When a column is created using a COMPUTE clause, its value in any row is the value of the supplied expression. Columns created with this constraint are read-only columns for applications: the value is changed by the database server whenever the row is modified. The COMPUTE expression should not return a non-deterministic value. For example, it should not include a special value such as CURRENT TIMESTAMP, or a non-deterministic function. If a COMPUTE expression returns a non-deterministic value, then it cannot be used to match an expression in a query.
The COMPUTE clause is ignored for remote tables.
Any UPDATE statement that attempts to change the value of a computed column fires any triggers associated with the column.
CHECK ON COMMIT clause The CHECK ON COMMIT option overrides the wait_for_commit database option, and causes the database server to wait for a COMMIT before checking RESTRICT actions on a foreign key. The CHECK ON COMMIT option delays foreign key checking, but does not delay other actions such as CASCADE, SET NULL, SET DEFAULT, or check constraints.
FOR OLAP WORKLOAD clause When you specify FOR OLAP WORKLOAD in the REFERENCES clause of a foreign key definition, the database server performs certain optimizations and gathers statistics on the key to help improve performance for OLAP workloads, particularly when the optimization_workload option is set to OLAP.
PCTFREE clause Specifies the percentage of free space you want to reserve for each table page. The free space is used if rows increase in size when the data is updated. If there is no free space in a table page, every increase in the size of a row on that page requires the row to be split across multiple table pages, causing row fragmentation and possible performance degradation.
The value percent-free-space is an integer between 0 and 100. The former value specifies that no free space is to be left on each page—each page is to be fully packed. A high value causes each row to be inserted into a page by itself. If PCTFREE is not set, or is later dropped, the default PCTFREE value is applied according to the database page size (200 bytes for a 4 KB (and up) page size). The value for PCTFREE is stored in the ISYSTAB system table.
RemarksThe CREATE TABLE statement creates a new table. A table can be created for another user by specifying an owner name. If GLOBAL TEMPORARY is specified, the table is a temporary table. Otherwise, the table is a base table.
Tables created by preceding the table name in a CREATE TABLE statement with a pound sign (#) are declared temporary tables, which are available only in the current connection. Temporary tables created with the pound sign (#) are identical to those created with the ON COMMIT PRESERVE ROWS clause.
Two local temporary tables within the same scope cannot have the same name. If you create a temporary table with the same name as a base table, the base table only becomes visible within the connection once the scope of the local temporary table ends. A connection cannot create a base table with the same name as an existing temporary table.
Columns in SQL Anywhere allow NULLs by default. This setting can be controlled using the allow_nulls_by_default database option.
PermissionsDBA or RESOURCE authority
Side effectsAutomatic commit (even when creating global temporary tables).
See also Standards and compatibilitySQL/2008 CREATE TABLE is a core feature of the SQL/2008 standard, though some of its components supported in SQL Anywhere are optional SQL language features. A subset of these features include:
Temporary table support is SQL language feature F531.
Support for IDENTITY columns is SQL feature T174, though SQL Anywhere uses slightly different syntax from that in the standard.
Foreign key constraint support includes SQL language features T191 "Referential action: RESTRICT", F741 "Referential MATCH types", F191 "Referential delete actions", and F701 "Referential update actions". Note that SQL Anywhere does not support MATCH PARTIAL.
SQL Anywhere does not support SQL language feature T591 ("UNIQUE constraints of possibly null columns"). In SQL Anywhere, all columns that are part of a PRIMARY KEY or UNIQUE constraint must be declared NOT NULL.
The following components of CREATE TABLE are vendor extensions:
The { IN | ON } dbspace-name clause.
The ENCRYPTED, NOT TRANSACTIONAL, and SHARE BY ALL clauses.
The COMPRESSED, INLINE, PREFIX, and NO INDEX clauses of a column definition.
Various implementation-defined DEFAULT values, including AUTOINCREMENT, GLOBAL AUTOINCREMENT, CURRENT DATABASE, CURRENT REMOTE USER, CURRENT UTC TIMESTAMP, and most special values. A DEFAULT clause that references a SEQUENCE generator is also a vendor extension.
The specification of MATCH UNIQUE.
Sortedness specification (ASC or DESC) on a PRIMARY KEY or FOREIGN KEY clause.
The ability to specify FOREIGN KEY columns in an order different from that specified in the referenced table's PRIMARY KEY clause.
ExamplesThe following example creates a table for a library database to hold book information.
CREATE TABLE library_books ( -- NOT NULL is assumed for primary key columns isbn CHAR(20) PRIMARY KEY, copyright_date DATE, title CHAR(100), author CHAR(50), -- column(s) corresponding to primary key of room -- are created automatically FOREIGN KEY location REFERENCES room ); |
The following example 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 ( date_borrowed DATE NOT NULL DEFAULT CURRENT DATE, date_returned DATE, book CHAR(20) REFERENCES library_books (isbn), -- The check condition is UNKNOWN until -- the book is returned, which is allowed CHECK( date_returned >= date_borrowed ) ); |
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 ), -- When an order is deleted, delete all of its -- items. FOREIGN KEY ( order_num ) REFERENCES Orders ( order_num ) ON DELETE CASCADE ); |
The following example creates a table named t1 at a fictitious remote server, SERVER_A, and creates a proxy table named t1 that is mapped to the remote table.
CREATE TABLE t1 ( a INT, b CHAR(10) ) AT 'SERVER_A.db1.joe.t1'; |
The following example creates two tables named Table1 and Table2, adds foreign keys to Table2, and inserts values into Table1. The final statement attempts to insert values into Table2. An error is returned because the values that you attempt to insert are not a simple match with Table1.
CREATE TABLE Table1 ( P1 INT, P2 INT, P3 INT, P4 INT, P5 INT, P6 INT, PRIMARY KEY ( P1, P2 ) ); CREATE TABLE Table2 ( F1 INT, F2 INT, F3 INT, PRIMARY KEY ( F1, F2 ) ); ALTER TABLE Table2 ADD FOREIGN KEY fk2( F1,F2 ) REFERENCES Table1( P1, P2 ) MATCH SIMPLE; INSERT INTO Table1 (P1, P2, P3, P4, P5, P6) VALUES ( 1,2,3,4,5,6 ); INSERT INTO Table2 (F1,F2) VALUES ( 3,4 ); |
The following statements show how MATCH SIMPLE and MATCH SIMPLE UNIQUE differ in how multi-column foreign keys are handled when some, but not all, of the columns in the key are NULL:
CREATE TABLE pt( pk INT PRIMARY KEY, str VARCHAR(10));
INSERT INTO pt VALUES(1,'one'), (2,'two');
COMMIT;
CREATE TABLE ft1( fpk INT PRIMARY KEY, FOREIGN KEY (ref) REFERENCES pt MATCH SIMPLE);
INSERT INTO ft1 VALUES(100,1), (200,1); //This statement will insert 2 rows.
CREATE TABLE ft2( fpk INT PRIMARY KEY, FOREIGN KEY (ref) REFERENCES pt MATCH UNIQUE SIMPLE);
INSERT INTO ft2 VALUES(100,1), (200,1); //This statement will fail because the values for the second column are not unique.
|
The following statements show how MATCH SIMPLE and MATCH UNIQUE SIMPLE differ:
CREATE TABLE pt2(
pk1 INT NOT NULL,
pk2 INT NOT NULL,
str VARCHAR(10),
PRIMARY KEY (pk1,pk2));
INSERT INTO pt2 VALUES(1,10,'one-ten'), (2,20,'two-twenty');
COMMIT;
CREATE TABLE ft3(
fpk INT PRIMARY KEY,
ref1 INT,
ref2 INT );
ALTER TABLE ft3 ADD FOREIGN KEY (ref1,ref2)
REFERENCES pt2 (pk1,pk2) MATCH SIMPLE;
CREATE TABLE ft4(
fpk INT PRIMARY KEY,
ref1 INT,
ref2 INT );
ALTER TABLE ft4 add FOREIGN KEY (ref1,ref2)
REFERENCES pt2 (pk1,pk2) MATCH FULL;
INSERT INTO ft3 VALUES(100,1,10);
// MATCH SIMPLE test succeeds; all column values match the corresponding values in pt2.
INSERT INTO ft3 VALUES(200,null,null);
// MATCH SIMPLE test succeeds; at least one column in the key is null.
INSERT INTO ft3 VALUES(300,2,null);
// MATCH SIMPLE test succeeds; at least one column in the key is null.
INSERT INTO ft4 VALUES(100,1,10);
// MATCH FULL test succeeds; all column values match the corresponding values in pt2.
INSERT INTO ft4 VALUES(200,null,null);
// MATCH FULL test succeeds; all column values in the key are null.
INSERT INTO ft4 VALUES(300,2,null);
// MATCH FULL test fails; both columns in the key must be null or match the corresponding values in pt2. |
 |
Discuss this page in DocCommentXchange.
|
Copyright © 2012, iAnywhere Solutions, Inc. - SQL Anywhere 12.0.1 |