After the physical file space has been allocated on a disk for the databank, (
CREATE DATABANK), you can create the tables. The basic
CREATE TABLEstatement defines the columns in the table, the primary key, any unique or foreign keys and which databank the table is to be stored in. Table names and column names may be up to 128 characters long.
As a convention, we have defined primary key column(s) as the first column(s) in the example definitions. However, this is not a necessity; primary key columns may be defined anywhere in the column list. Primary keys are always
NOT NULL, so there is no need to explicitly state that in the table definition.
Create Table Statement
Create the table CURRENCIES with three columns in the MIMER_STORE schema.
The table shall be as follows:
- Name the first column
CODE, make it of the
CHARACTERdata type with a maximum of three characters.
- Name the second column
CURRENCY, make it of the
CHARACTERdata type with a maximum of 32 characters and don't allow null values to be stored in the column.
- Name the third column
EXCHANGE_RATEand make it of the data type
DECIMALwith a total of twelve digits, four of which can be decimal values.
- Declare the
CODEcolumn as the primary key and place this table in the
MIMER_STOREdatabank.CREATE TABLE mimer_store.currencies ( code CHARACTER(3), currency CHARACTER(32) NOT NULL, exchange_rate DECIMAL(12, 4), PRIMARY KEY(code)) IN mimer_store;
CREATE TABLEclause defines the name of the table followed by a column list, which includes the names of the columns in the table, their data type, if they should allow the null indicator and the primary key declaration. Each item in the column-list is separated from the next by a comma, and the entire list is enclosed in parentheses.
A table definition may only include one primary key clause. The primary key can be made up of more than one column.
INclause states which databank the table is to be stored in. This clause may be omitted; if the
INclause is not specified, Mimer SQL will select the `best' databank in which to place the table.
The empty table now exists in the databank. Data is inserted into the table with the
INSERTstatement, see Inserting Data.
The preceding example shows the simplest form of column list. The following variants may also be used:
- columns belonging to domains
- columns defined with collations
- default values (overriding any domain default for the column)
- columns not belonging to the primary key defined as
- unique constraints (in addition to the primary key)
- foreign key constraints
- check constraints.
The ITEMS Table
ITEMStable in the example database is defined with many of the options that can be used in creating tables. See the Mimer SQL Reference Manual, CREATE TABLE, for more information.
ITEMStable is defined as follows:CREATE TABLE items ( item_id internal_id DEFAULT NEXT VALUE FOR item_id_seq, product_id internal_id CONSTRAINT itm_product_id_not_null NOT NULL, format_id format_id CONSTRAINT itm_format_id_not_null NOT NULL, release_date DATE, status CHAR DEFAULT 'A' CONSTRAINT itm_status_not_null NOT NULL CONSTRAINT itm_status_valid -- Available, Deleted CHECK (status IN ('A', 'X')), price euros CONSTRAINT itm_price_valid CHECK (price >= 4.99 AND price <= 366.00), stock SMALLINT CONSTRAINT itm_stock_not_null NOT NULL CONSTRAINT itm_stock_valid CHECK (stock >= 0), reorder_level SMALLINT CONSTRAINT itm_reorder_level_not_null NOT NULL, ean_code BIGINT CONSTRAINT itm_ean_code_not_null NOT NULL, producer_id internal_id DEFAULT NULL, image_id internal_id DEFAULT NULL, CONSTRAINT itm_primary_key PRIMARY KEY(item_id), CONSTRAINT itm_ean_code_exists UNIQUE (ean_code), CONSTRAINT itm_products FOREIGN KEY (product_id) REFERENCES products(product_id) ON DELETE CASCADE ON UPDATE NO ACTION, CONSTRAINT itm_formats FOREIGN KEY (format_id) REFERENCES formats ON DELETE CASCADE ON UPDATE NO ACTION, CONSTRAINT itm_producers FOREIGN KEY (producer_id) REFERENCES producers ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT itm_images FOREIGN KEY (image_id) REFERENCES images ON DELETE SET DEFAULT ON UPDATE NO ACTION) IN mimer_store;
The ordering of column specifications, key clauses and check conditions is not fixed. If desired, the key and check clauses can be written in association with the respective column specifications.
Each constraint is given a name which allows it to be dropped and modified separately. The constraint name is also useful if a program wants to find out which constraint failed for a particular statement.
Columns should in general be defined as
NOT NULLunless there is a specific reason for using the null value in the column (e.g. is the value not known, not applicable or given some other meaning). The presence of null values can often complicate the formulation of queries, see Handling Null Values.
Note: Take particular care to exclude null from numerical columns which are to be used for mathematical operations.
Domains are used for many columns to help in maintaining database integrity. By using the same domain for columns in different tables, the column data types are guaranteed to be consistent. See Creating Domains for more information
The Primary Key Constraint
The purpose of a primary key is to define a key value that uniquely identifies each table row, therefore the primary key value for each row in the table must be unique.
The primary key constraint can consist of more than one column in the table. The choice of columns to use as the primary key is determined by the relational model for the database, which is outside the scope of this manual.
A unique constraint can be defined for one or more columns in the table. The list of columns that make up the unique constraint are specified in the
UNIQUEclause for the table when it is created.
UNIQUEin the definition of a column in the table is equivalent to supplying a list of one column in the
UNIQUEclause for the table and effectively specifies a one-column unique constraint.
Foreign Keys - Referential Constraints
Use foreign keys to maintain integrity between the contents of related tables.
The effect of a foreign key is to constrain table data in a way that only allows a row in the referencing table which has a foreign key value that matches the specified key value of a row in the referenced table.
A referencing table row which has a foreign key value with the null value in at least one of the columns will always fulfil the referential constraint and therefore be acceptable as a row in the referencing table.
A foreign key constraint can be defined with a foreign key clause at
CREATE TABLEor added afterwards using
The table referenced in a foreign key clause can be an existing table or a table defined in the current statement (allowing self-referencing foreign keys at
CREATE TABLEand circular foreign keys at
The number of columns listed as
FOREIGN KEYmust be the same as the number of columns in the primary key or unique key of the
REFERENCEStable. See the
CREATE TABLEsyntax in the Mimer SQL Reference Manual for details.
FOREIGN KEYcolumn corresponds to the nth column in the primary key of the
REFERENCEStable, and the data types and lengths of corresponding columns must be identical.
A table definition may contain several
FOREIGN KEYreferences. Each column in the table may be used in many
FOREIGN KEYclauses, but only once per
Note: A table containing a foreign key reference or referenced in a foreign key must be stored in a databank with either the TRANSACTION or LOG option.
Foreign Key Example
ITEMStable has four foreign key references:CREATE TABLE items ( item_id internal_id DEFAULT NEXT VALUE FOR item_id_seq, product_id internal_id CONSTRAINT itm_product_id_not_null NOT NULL, format_id format_id CONSTRAINT itm_format_id_not_null NOT NULL, . . producer_id internal_id DEFAULT NULL, image_id internal_id DEFAULT NULL, . . CONSTRAINT itm_products FOREIGN KEY (product_id) REFERENCES products(product_id) ON DELETE CASCADE ON UPDATE NO ACTION, CONSTRAINT itm_formats FOREIGN KEY (format_id) REFERENCES formats ON DELETE CASCADE ON UPDATE NO ACTION, CONSTRAINT itm_producers FOREIGN KEY (producer_id) REFERENCES producers ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT itm_images FOREIGN KEY (image_id) REFERENCES images ON DELETE SET DEFAULT ON UPDATE NO ACTION) . .
These maintain referential integrity as follows:
- FOREIGN KEY (product_id) REFERENCES products(product_id)
- FOREIGN KEY (format_id) REFERENCES formats
- FOREIGN KEY (producer_id) REFERENCES producers
- FOREIGN KEY (image_id) REFERENCES images
Specifying ON DELETE
When defining a foreign key constraint it is possible to specify in an
ON DELETEclause what action that shall take place if the corresponding record in the referenced table is deleted.
The possible actions are
- NO ACTION
- SET NULL
- SET DEFAULT
Check constraints in table definitions are used to make sure that data in a column (or row) in the table fits certain conditions.CREATE TABLE items ( . . status CHAR (1) DEFAULT 'A' CONSTRAINT itm_status_not_null NOT NULL CONSTRAINT itm_status_valid -- Available, Deleted CHECK (status IN ('A', 'X')), price euros CONSTRAINT itm_price_valid CHECK (price >= 4.99 AND price <= 366.00), stock SMALLINT CONSTRAINT itm_stock_not_null NOT NULL CONSTRAINT itm_stock_valid CHECK (stock >= 0), . .
The check clause defined on the
PRICEcolumn extends any limitations imposed by the
EUROSdomain definition. The extension applies only to this table, and does not affect other columns in the database that belong to the
EUROSdomain:CREATE DOMAIN euros AS NUMERIC(7, 2) CONSTRAINT euros_value_not_null CHECK (VALUE IS NOT NULL) CONSTRAINT euros_value_valid CHECK (VALUE > 0.0);
The constraint names, e.g.
ITEMStable, can be used in an
ALTER TABLEstatement to drop the check constraint. All constraints, primary key, unique, not null and foreign key constraints can be named in this manner.
If no constraint name is given, a unique name is generated by the system. This name can be seen by using the describe statement in BSQL. See Mimer BSQL.
Ensure that either the customer's e-mail address and password are both defined or that neither is defined:CREATE TABLE customers ( . . email VARCHAR(128) COLLATE english CHECK (char_length(trim(email)) > 0), password VARCHAR(18) CHECK (char_length(trim(password)) > 0), . . CONSTRAINT cst_email_password_cross_check CHECK ( (email IS NULL AND password IS NULL) OR (email IS NOT NULL AND password IS NOT NULL)) . .
Mimer Information Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40