Use the CREATE TABLE command to define a new table in an existing database.
Options
,
Indicates that this part of the command is repeatable.
AFTER
Sets the trigger to activate after the INSERT, UPDATE or DELETE action.
BEFORE
Sets the trigger to activate before the INSERT, UPDATE or DELETE action. This is the default setting if the BEFORE/AFTER parameter is unused.
CASCADE
Maintains primary/foreign key relationships automatically. For example, if you either UPDATE or DELETE a primary key value from a table, the corresponding foreign key values are updated or deleted automatically. A CASCADE can be applied to UPDATE, DELETE or BOTH to specific primary keys. By not specifying either UPDATE or DELETE, both CASCADE restrictions will be enforced upon the primary/foreign key tables. Separate UPDATE and DELETE data restrictions can allow a CASCADE to be enforced for records that are updated, but not enforced when records are deleted, in order to avoid an accidental or undesired record delete. CASCADE can only be added to tables with primary keys.
CASE
Specifies that the data values will be case sensitive.
CHECK (condition)
Sets a condition to be satisfied before an update or insertion of a row can occur, which creates an R:BASE rule.
(collist)
Specifies a list of one or more column names, separated by a comma (or the current delimiter), used in the unique key specification. This option is only used when referencing a unique key.
colname
Specifies a column name. The column name is limited to 128 characters.
datatype
Specifies an R:BASE data type.
DEFAULT
Specifies a default value for the column if no value is provided by the user. The specified default value must match the column's data type (e.g. "PA" for a column that stores state address abbreviations). With TEXT columns, do not enclose the default value in the QUOTES character. The system variables #DATE, #TIME, and #NOW may be specified as default values. Static and global variables may also be specified as default values. When using system variables, static variables, or global variables, the value must be a dotted variable (the period must be included).
DUPLICATE tblname.colname
Copies the data type, length, default value, and NOT NULL flag if any. DUPLICATE does not support computed columns, and will not duplicate any comment/description for the column.
= (expression)
Determines a value using a text or arithmetic formula. The expression can include other columns from the table, constant values, functions, or system variables such as #date, #time, and #pi.
(<FKMSG>)
Creates a constraint violation message to appear whenever a foreign-key data integrity violation occurs. The message can suit the meaning of your data, such as "You must enter a valid number" for a foreign-key constraint violation. You can define two messages: one for inserting and one for updating. A constraint must be dropped, then recreated in order to modify the violation message.
FOREIGN INDEX
With the FASTFK setting on, creates a foreign key that has an index using row pointers for data retrieval on selected columns.
FOREIGN KEY
Specifies a column or set of columns required to match values in a particular primary key or unique key defined in a table.
(<NNMSG>)
Creates a constraint violation message to appear whenever a not-null data integrity violation occurs. The message can suit the meaning of your data, such as "You must enter a valid number" for a not-null constraint violation. A constraint must be dropped, then recreated in order to modify the violation message.
NOT NULL
Prevents a column from accepting null values, but permits it to accept duplicate values.
If this option is specified without a setting for a default value, you cannot insert rows without specifying values for the given column.
(<PKMSG>)
Creates a constraint violation message to appear whenever a primary-key data integrity violation occurs. The message can suit the meaning of your data, such as "You must enter a valid number" for a primary-key constraint violation. You can define three messages: one for uniqueness, one for deleting, and one for updating. A constraint must be dropped, then recreated in order to modify the violation message.
PRIMARY KEY
Specifies the column(s) to designate as a primary key.
procname
The procedure name. If a procedure by this name already exists in the database, an error is generated.
REFERENCES tablename
Identifies the primary key or unique key table to which the foreign key refers.
(size)
Defines the length of a column of the TEXT data type (if not the default 8). Defines the precision and scale of a column of the DECIMAL or NUMERIC data type, if not the default of precision 9 and scale 0 (9,0). VARBIT, VARCHAR, and BIT either require or can have a size.
tblname
Specifies a table name. The table name is limited to 128 characters.
TEMPORARY
Creates a temporary table that disappears when the database is disconnected.
TRIGGER
Runs a stored procedure when an UPDATE, DELETE, or INSERT command is run on the table.
(<UMSG>)
Creates a constraint violation message to appear whenever a unique-key data integrity violation occurs. The message can suit the meaning of your data, such as "You must enter a valid number" for a unique-key constraint violation. You can define three messages: one for uniqueness, one for deleting, and one for updating. A constraint must be dropped, then recreated in order to modify the violation message.
UNIQUE
Requires the values in a column to be unique by defining a unique key constraint.
USER
NULL
(value)
Default USER: Specifies the default value to be the user identifier.
Default NULL: Specifies the default value to be null.
Default (value): Specifies the default to be the indicated value.
About the CREATE TABLE Command
To define a table, you need to specify column definitions. Table and column names must begin with an upper- or lowercase letter. Names can contain letters, numbers, and the following special characters: #, $, _, and %. R:BASE verifies that a table or column name is unique by reading all characters.
When you define a table, you can also add table constraints. However, you cannot add a foreign key to a temporary table.
To define more than one column in a command, use commas (or the current delimiter character) to separate the column definitions.
Computed Columns
A computed column is a column containing a value that R:BASE calculates from an expression defined for the column. In the expression, you can use other columns from the table, constant values, functions, and the system variables #date, #time, and #pi. Global variables are not allowed in an expression.
You must assign a data type that is compatible with the result of the computation. The columns used for calculating the computed column must precede the computed column in the table.
Indexing Columns
If you want to assign an index to a column, use the CREATE INDEX command.
Database Access Rights with CREATE TABLE
CREATE TABLE requires either the CREATE access right or the owner's user identifier when access rights have been assigned with the GRANT command.
Examples
The command below defines a table named employee with the following columns and data types: empid (INTEGER), emptitle(TEXT 30), empfname (TEXT 10), emplname (TEXT 16), empaddress (TEXT 30), empcity (TEXT 20), empstate (TEXT 2), empzip (TEXT 10), empphone (TEXT 12), empext(INTEGER), hiredate (DATE), and entrydate (DATE). In addition, the NOT NULL option specifies that the columns empfname, emplname, and hiredate must contain a value. The NOT NULL UNIQUE option specifies that the empid and empext columns must contain unique values.
CREATE TABLE employee (empid INTEGER NOT NULL UNIQUE, +
emptitle TEXT (30), empfname TEXT (10) NOT NULL, emplname TEXT +
(16) NOT NULL, empaddress TEXT (30), empcity TEXT (20), empstate +
TEXT (2), empzip TEXT (10), empphone TEXT (12), empext INTEGER +
NOT NULL UNIQUE, hiredate DATE NOT NULL, entrydate DATE)
The following command creates a table using the column constraint CHECK on the empid column.
CREATE TABLE employee (empid INTEGER CHECK (empid > 0), +
empname TEXT (40), empage INTEGER)
The example below creates a table using the column constraint CHECK on the empid and empage columns.
CREATE TABLE employee (empid INTEGER CHECK (empid > 0), +
empname TEXT (40), empage INTEGER CHECK (empage >0 and empage < 100))
Table constraints are defined if it is necessary to reference multiple columns within the same expression. The UNIQUE (collist) option is entered at the end of the following command so that the values in the empid, empfname, emplname columns are unique as a group in a row. Because this option follows a column definition, precede the option with a comma.
CREATE TABLE employee (empid INTEGER NOT NULL, +
emptitle TEXT (30), empfname TEXT (10) NOT NULL, emplname TEXT +
(16) NOT NULL, empaddress TEXT (30), empcity TEXT (20), empstate +
TEXT (2), empzip TEXT (10), empphone TEXT (12), empext INTEGER +
NOT NULL UNIQUE, hiredate DATE NOT NULL, entrydate DATE, +
UNIQUE (empid, empfname, emplname))
The following command creates a table that would contain an employee's total years of employment. The command places a column constraint on the empid and empage columns, and a table constraint on the yrshere and yrsanywhere columns. The value entered for yrshere must be less than or equal to the value entered for yrsanywhere.
CREATE TABLE employee (empid INTEGER CHECK (empid > 0), +
empname TEXT (40), empage INTEGER CHECK +
(empage > 0 and empage < 100), yrshere INTEGER, +
yrsanywhere INTEGER, CHECK (yrshere <= yrsanywhere))
