create table

Create a new database table

Syntax

create_table:
          CREATE TABLE [database_name.]table_name [OF] MAXROWS [=] maxrows_num
          (column_def [, column_def]... [, tab_constraint]...)

Description

The CREATE TABLE statement is used to define a table to be included in the database. Any domain types that are used in column declarations included in the CREATE TABLE statement must have already been declared through the issuance of a prior CREATE DOMAIN statement.

The table_name is a user-specified identifier that names the table. The contents of the table is comprised of the columns that are declared within it. Columns are declared to be of a specific data type which is either explicitly given or specified through use of a previously declared domain name. A DEFAULT value can also optionally be specified unless the column was declared with a domain type.

Columns can be specified with one or more constraints which declare the column to be:

NOT NULL—null values are not allowed for the column,
a PRIMARY/UNIQUE or non-unique KEY—on which an index will be automatically created,
a FOREIGN KEY that REFERENCES the primary/unique key of the specified table.

Columns declared as NOT NULL will cause any INSERT or UPDATE statement that attempts to assign a NULL value to that column to return an error.

Foreign key references are automatically implemented by RDM SQL for quick access and maintenance of referential integrity. A triggered_action can be specified with foreign key columns in order to indicate what should happen when the referenced row is updated or deleted. The default action is RESTRICT meaning that primary key rows that have existing foreign key references cannot be updated or deleted. If ON ... CASCADE is specified, then all of the referenced rows are updated or deleted when the primary key row is updated (i.e., the primary key column value) or deleted. Note that the referencing table may itself have a primary key declared that is referenced by foreign keys in other tables that may not have a CASCADE triggered action specified. Thus, a delete of the referenced row of a cascade-delete-allowed table may be denied due to a restrict foreign key on a row of a referencing table. If ON ... SET NULL is specified, then all of the referencing foreign key columns will be set to null. This option is not allowed when the foreign key column has been declared as NOT NULL.

A key_def on a table is used to declare primary, unique or non-unique keys and foreign keys on one or more columns. The [PRIMARY | UNIQUE] KEY clause is used to identify the columns from the table on which a key is to be formed. A table can have only one PRIMARY KEY. By default keys are maintained in a b-tree index file which maintains the keys in sorted order based on the data type of the columns comprising the key. You can also specify that a key be stored in a HASH index which is designed for very fast lookups of specific keys but cannot be used for sorting or range searches. A key can also be stored in an AVL-tree which is particularly useful when the number of index values is relatively small and the database is intended to be use in-memory.

A column can be declared to be a ROWID PRIMARY KEY. A rowid is a BIGINT value that uniquely identifies the table row. The value of a rowid primary key column is automatically generated by the system when an INSERT is executed. In all other ways, a rowid primary key column behaves just like a BIGINT PRIMARY KEY column except that no b-tree index file is needed and access performance is optimal as the row is typically accessed in no more than one disk read.

A column can also be declared to be a ROWID foreign key by including a REFERENCES clause with the column declaration.

Only the ON UPDATE RESTRICT clause (the default) can be specified as it is not possible to change a ROWID PRIMARY KEY value.

The MAXROWS option sets maximum capacity for the table. When the maximum capacity is reached for inserts in to the table, a eROWLIMIT error code is returned. A typical usage would be to limit the number of rows in a table for an in-memory database.

The data type DATE assumes the Gregorian calendar even for dates prior to the introduction of the Gregorian calendar. This means that databases that store historical dates prior to the introduction of the Gregorian calendar may not compute SELECT with date ranges, DAYOFWEEK, and WEEK correctly.

Example

create table acctmgr(    mgrid       char(7) primary key,    name        char(24),    hire_date   date,    commission  decimal(4,3));create table patron(    patid       char(3) primary key,    name        char(30),    street      char(35),    city        char(17),    state       char(2),    country     char(2),    pc          char(10),    email       char(63),    phone       char(15),    mgrid       char(7) references acctmgr on update cascade);create table author(    last_name   char(13) primary key,    full_name   char(35),        gender      char(1),    yr_born     smallint,    yr_died     smallint,    short_bio   varchar(216),    constraint yob_gender_key key (yr_born, gender));create table genres(    text       char(31) primary key);create table subjects(    text       char(51) primary key);create table book(    bookid      char(14) primary key,    last_name   char(13)         references author on delete cascade on update cascade,    title       varchar(105) key,    descr       char(61),    publisher   varchar(136),    publ_year   smallint key,    lc_class    char(33),    date_acqd   date,    date_sold   date,    price       decimal(10,2),    cost        decimal(10,2));create table related_name(    bookid      char(14)         references book on delete cascade on update cascade,    name        char(61));create table genres_books(    bookid      char(14)         references book on delete cascade on update cascade,    genre       char(31)         references genres);create table subjects_books(    bookid      char(14)         references book on delete cascade on update cascade,    subject     char(51)         references subjects);create table note(    noteid      integer primary key,    bookid      char(14)         references book on delete cascade on update cascade,    patid       char(3)         references patron on delete cascade on update cascade);create table note_line(    noteid      integer         references note on delete cascade on update cascade,    text        char(61));create table sale(    bookid      char(14)         references book on delete cascade on update cascade,    patid       char(3)         references patron on delete cascade on update cascade);create table auction(    aucid       integer primary key,    bookid      char(14)         references book on delete cascade on update cascade,    mgrid       char(7)         references acctmgr on update cascade,    start_date  date,    end_date    date,    reserve     decimal(10,2),    curr_bid    decimal(10,2),    bid_time    time);create table bid(    aucid       integer         references auction on delete cascade on update cascade,    patid       char(3)         references patron on delete cascade on update cascade,    offer       decimal(10,2),    bid_ts      timestamp);