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 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 1 to 18 character column name.
datatype
Specifies an R:BASE data type.
DEFAULT
Specifies a default value for the column if no value is provided by the user.
= (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 either the CHARACTER data type (if not the default 1) or 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 1 to 18 character table name.
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 can be a maximum of 18 characters and 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 18 characters.
When you define a table, you can also add table constraints.
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. For more information about assigning access rights, see the "GRANT" entry.
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.
