602SQL Documentation Index  

Database Tables

Database tables are used for storing data on a server. Each table has a given structure: a certain amount of columns (max. 255 up to server version 9.0, 65535 since version 9.5) of specified types. Besides that integrity constraints, referential integrity rules and indexes can be defined for a table.

System queries _IV_TABLE_COLUMNS, _IV_INDICIES, _IV_CHECK_CONSTRAINS a _IV_FOREIGN_KEYS return a table structure information.

Each table is designated with a name and belongs to some scheme (except for the system tables).

Tables are created by calling the CREATE TABLE statement and deleted with the DROP TABLE statement. Table structure and properties can also be modified using the ALTER TABLE, CREATE INDEX and DROP INDEX statements.

Record Status in a Table

Each database table is composed of records. Each record can be in one of these states:

Only valid records are present in a table from the SQL view.

The DELETE statement and the Delete API function delete a valid record. The record then becomes a deleted or dropped record based on the compatibility attributes settings.

All deleted records are dropped by the Free_deleted API function.

Deleted records that haven't been dropped yet may be restored by calling the Undelete API function.

The Compact_table API function compresses a table - it moves records in the table, so there are no deleted or dropped records before any valid records in the table after the action is complete and the table is compact (there may be a small amount of deleted records at the "end" of a table).

The number of records in individual states in a certain table may be get by the Table statistics action from the Control Panel. Deleted records may be restored interactively when a table is opened in a special way - the Deleted? column appears in the grid, that allows you to change the record state.

System query _IV_TABLE_SPACE returns the space that the table occupies in a database file (including the columns of a variable length and indexes).

List of topics: