Data Types

SQL Language in 602SQL

Privileges and Privilege Subjects in SQL

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:

• Valid record - a record, that is valid and that can be used for work.
• Deleted record - a record, that was once valid, but then was deleted. A deleted record is not visible and SQL language doesn't work with it, however it can be restored without information loss and become a valid record again.
• Freed (dropped) record - a free space in a table, where a new record can be inserted. These records still occupy the space in a db file.

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:

• CREATE TABLE Statement
• Table Column Description
• Database Table Indexes
• Integrity Constraints in Tables
• Tables Referential Integrity
• When do the Integrity Constraint Checks Take Place?
• Sequence of Actions and Checks when Executing Statements
• Complete CREATE TABLE Statement
• ALTER TABLE Statement
• DROP TABLE Statement

Data Types

SQL Language in 602SQL

Privileges and Privilege Subjects in SQL