Create a new database table
create_table: CREATE TABLE [database_name.]table_name [OF] MAXROWS [=] maxrows_num (column_def [, column_def]... [, tab_constraint]...)
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.
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.
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);