Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 11.0.1 (Français) » SQL Anywhere Server - SQL Reference » Using SQL » SQL statements » SQL statements (A-D)

 

CREATE TABLE statement

Use this statement to create a new table in the database and, optionally, to create a table on a remote server.

Syntax
CREATE [ 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 )
| built-in-function( constant-expression )
| AUTOINCREMENT
| CURRENT DATABASE
| CURRENT REMOTE USER
| CURRENT UTC TIMESTAMP
| GLOBAL AUTOINCREMENT [ ( partition-size ) ]
| NULL
| TIMESTAMP
| UTC TIMESTAMP
| LAST USER
special-value:
CURRENT { 
   DATE 
   | TIME 
   | TIMESTAMP
   | UTC TIMESTAMP 
   | USER 
   | PUBLISHER 
}
| USER
column-constraint :
[ CONSTRAINT constraint-name ] {
   UNIQUE [ CLUSTERED ]
  | PRIMARY KEY [ CLUSTERED ] [ ASC | DESC ]
  | REFERENCES table-name [ ( column-name ) ] 
     [ MATCH [ UNIQUE ] { SIMPLE | FULL } ] 
     [ action-list ] [ CLUSTERED ] 
   }
| [ CONSTRAINT constraint-name ] 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-space
percent-free-space : integer
Parameters
  • IN 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.

    For more information about dbspaces, see:

  • 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. See Enabling table encryption in the database.

  • 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. For information about the benefits of the NOT TRANSACTIONAL clause, see Working with temporary tables.

  • 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';

    For information about remote servers, see CREATE SERVER statement. For information about proxy tables, see CREATE EXISTING TABLE statement and Specify proxy table locations.

    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.

    For information about the characteristics of temporary tables, see Working with temporary tables.

  • IF NOT EXISTS clause   Use this clause to create permanent, global temporary, and local temporary tables. No changes are made if the named table already exists, and an error is not returned.

    For information about the characteristics of temporary tables, see Working with temporary tables.

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

    • data-type   The type of data stored in the column. See SQL data types.

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

      Note

      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.

    Note

    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.

    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   For more information about the special-value, see Special values.

    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:

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

    • CURRENT REMOTE USER    The CURRENT REMOTE USER default is set by the SQL Remote Message Agent (dbremote) when it executes the undocumented REMOTE USER statement. The value will be NULL except for connections from DBREMOTE. See Message Agent (dbremote).

    • 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. See @@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 the case for primary key columns, a SQL error is generated.

      For information about rebuilding databases that use AUTOINCREMENT, see Reloading tables with autoincrement columns.

    • IDENTITY   The IDENTITY default is a Transact-SQL-compatible alternative to using the AUTOINCREMENT default. In SQL Anywhere, a column defined as IDENTITY is implemented as AUTOINCREMENT. See The special IDENTITY column.

    • 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 the case 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. See Understanding events.

      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.

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

    For more information about timestamp columns, see The special Transact-SQL timestamp column and data type.

    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. See default_timestamp_increment option [database] [MobiLink client].

    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. See default_timestamp_increment option [database] [MobiLink client].

    The global variable @@dbts returns a TIMESTAMP value representing the last value generated for a column using DEFAULT TIMESTAMP. See Global variables.

  • UTC TIMESTAMP clause   The behavior of UTC TIMESTAMP is the same as TIMESTAMP except that a UTC TIMESTAMP value is in Coordinated Universal (UTC) time.

  • string   See Strings.

  • global-variable   See Global variables.

  • 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. Generally speaking, a column constraint is one that refers to one column in a table, while a table constraint can refer to one or more columns in a table.

    • PRIMARY KEY constraint   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. For more information about the CLUSTERED option and clustered indexes, see Using clustered indexes.

      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.

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

      For information about how the database server can select columns automatically for the foreign key, see Omitting column names at foreign key creation (SQL).

      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. For more information about the CLUSTERED option and clustered indexes, see Using clustered indexes.

      A temporary table cannot have a foreign key that references a base table and a base table cannot have a foreign key that references a temporary table.

      • NOT NULL option   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:

        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.

      • 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 allows you to control what is considered a match when using a multi-column foreign key. It also allows you to specify uniqueness for the key, thereby eliminating the need to declare uniqueness separately. Following is a list match types you can specify:

        • UNIQUE option   The referencing table can have only one match for non-NULL key values (keys with at least one non-NULL column value are implicitly unique).

        • SIMPLE option   A match occurs for a row in the referencing table if at least one column in the key is NULL, or all the column values match the corresponding column values present in a row of the referenced table.

        • FULL option   A match occurs for a row in the referencing table if all column values in the key are NULL, or if all the column values match the values present in a row of the referenced table.

        • SIMPLE UNIQUE option   A match occurs if the criteria for both SIMPLE and UNIQUE are met.

        • FULL UNIQUE option   A match occurs if the criteria for both FULL and UNIQUE are met.

    • UNIQUE constraint   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. For more information about the CLUSTERED option, see Using clustered indexes.

      For information about unique indexes, see CREATE INDEX statement.

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

      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.

      For more information about TRUE, FALSE, and UNKNOWN conditions, see NULL value, and Search conditions.

    • 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. See Working with computed columns.

      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 option   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 does not delay CASCADE, SET NULL, or SET DEFAULT actions.

    If you use CHECK ON COMMIT without specifying any actions, then RESTRICT is implied as an action for UPDATE and DELETE.

  • FOR OLAP WORKLOAD option   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 is set to OLAP. See optimization_workload option [database].

    For more information, see OLAP support.

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

Remarks

The 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. See DECLARE LOCAL TEMPORARY TABLE statement.

Columns in SQL Anywhere allow NULLs by default. This setting can be controlled using the allow_nulls_by_default database option. See allow_nulls_by_default option [compatibility].

Permissions

Must have RESOURCE authority.

Must have DBA authority to create a table for another user.

Side effects

Automatic commit.

See also
Standards and compatibility
  • SQL/2003   Core feature.

    The following are vendor extensions:

    • The { IN | ON } dbspace-name clause.

    • The ON COMMIT clause.

    • Some of the default values.

Examples

The 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 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 (
   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 the 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';