A PSQL database consists of two basic parts:

The following topics explain named databases, bound databases, and how to create a database and use a data dictionary to manage data files as tables, columns, and indexes:

A named database has a logical name that allows users to identify it without knowing its actual location. When you name a database, you associate that name with a particular dictionary directory path and one or more data file paths. When you log in to PSQL using a database name, PSQL uses the name to find the database dictionary and data files. Your database must be named before you can do the following:

You use PSQL Control Center to name existing, unbound databases and to create new, bound databases. See PSQL User's Guide for more information.

Binding a database ensures that the MicroKernel enforces database security, referential integrity (RI), and triggers, regardless of the method used to access the data. The MicroKernel enforces these integrity controls as follows:

The dictionary and data files in a bound database cannot be referenced by other named databases. Also, bound data files cannot be referenced by other tables in the database.

When you create a bound database or bind an existing database, PSQL stamps every dictionary and data file with the name of the bound database. Also, PSQL stamps every data file with the name of the table associated with that data file. In addition, when you add new tables or dictionary files to the database, PSQL automatically binds them.

Use PSQL Control Center to create databases. See PSQL User's Guide.

To create tables in the database, use PSQL Control Center or the CREATE TABLE syntax defined in SQL Engine Reference. When you issue a CREATE TABLE statement, you must include commands that define columns. In addition, you can include commands that define referential integrity (RI) constraints.

When you create a database, PSQL allows you to assign a descriptive name to each database component. Users and applications refer to the components of the database using these names. This topic outlines the conventions to which you must adhere when naming database components.

For more information, see Identifier Restrictions by Identifier Type in Advanced Operations Guide.

The following database components must have unique names within a dictionary:

Names for parameters and variables must be unique within a SQL statement. Because PSQL keywords are reserved words, you cannot use them for naming database components or in parameter names and variables. See SQL Reserved Words in SQL Engine Reference for a list of reserved keywords.

When a column name is repeated in different tables, you can qualify it in each table by preceding it with the relevant table name or alias name. For example, you can refer to the ID column in the Student table as Student.ID. This is a fully qualified column name, and the table name (Student) is the column qualifier.

Following are the valid characters for the names of database components at the SQL level, and for variables and parameter names:

PSQL restricts the maximum length of database component names in a dictionary. See Identifier Restrictions by Identifier Type in Advanced Operations Guide and Limits/Conditions of PSQL Features in SQL Engine Reference.

PSQL is case-sensitive when you are defining database component names. If you create a table named TaBLe1, PSQL stores the table name in the dictionary as TaBLe1. With the exception of user names, user group names, and passwords, PSQL is case-insensitive after you define the component name. After defining the table TaBLe1, you can refer to it as table1.

User names, user group names, and passwords are case-sensitive in PSQL. For example, when you log in as the master user, you must specify the user name as Master.

When retrieving data, PSQL displays names for tables, views, aliases, and columns based on the case in which they were created.

PSQL returns the column names as follows:

PSQL uses the dictionary to store information about the database. The dictionary consists of several system tables that describe the tables and views of your database.

The system tables contain several types of database information, including table and index definitions, column characteristics, and integrity and security information. Table 37 describes the system tables PSQL creates. See also the chapter System Tables in SQL Engine Reference.

Because the system tables are part of the database, you can query them to determine their contents. If you have the appropriate rights, you can also create system tables or change their contents.

For a complete reference to the contents of each system table, see SQL Engine Reference.

Once you have created a dictionary, you can add tables, columns, and indexes to your database.

When you create a table, you must name it. Each table name must be unique within a database. For more information about rules for naming tables, see Naming Conventions.

When you are deciding which tables to create in your database, consider that different users can look at data in different combinations using views. A view looks like a table and can be treated as a table for most purposes (such as retrieving, updating, and deleting data). However, a view is not necessarily associated with a single table; it can combine information from multiple tables. For more information, see Retrieving Data.

You can create a table using PCC. See To start Table Editor for a new table in PSQL User's Guide.

You can assign aliases (also called alias names) to table names in the following elements of statements:

An alias can be any combination of up to 20 characters. Always separate the table name from the alias with a blank. Separate the alias and the column name with a period (.). Once you specify an alias for a particular table, you can use it elsewhere in the statement to qualify column names from that table.

The following example specifies the alias name s for the table Student and e for the table Enrolls.

You can use an alias to do the following:

You create columns when you create a table using a CREATE TABLE statement, or you can add columns to an existing table using an ALTER TABLE statement. In either case, you must specify the following characteristics:

For more information about data types, see the Btrieve API Guide.

Indexes optimize operations that either search for or order by specific values. Define indexes for any columns on which you frequently perform either of these operations. Indexes provide a fast retrieval method for a specific row or group of rows in query optimization. PSQL also uses indexes with referential integrity (RI). Indexes improve performance on joins and help to optimize queries. For more information about RI, see PSQL User's Guide.

In PSQL databases, the MicroKernel Engine creates and maintains indexes as part of the physical file for which they are defined. The MicroKernel Engine performs all maintenance for insert, update, or delete operations. These activities are transparent to any PSQL application.

To create an index, use a CREATE INDEX statement. This method creates a named index. You can delete named indexes after you create them. For more information about dropping indexes, see Inserting and Deleting Data.

While indexes allow you to sort rows and retrieve individual rows quickly, they increase the disk storage requirements for a database and decrease performance somewhat on insert, update, and delete operations. You should consider these trade-offs when defining indexes.

The next example uses a CREATE INDEX statement to add an index to a table that already exists:

For detailed information about the CREATE TABLE and CREATE INDEX statements, see SQL Engine Reference.

You can create an index on any single column or group of columns in the same table. An index that includes more than one column is called a segmented index, in which each column is called an index segment.

For example, the Person table in the Demodata sample database has the following three indexes:

The number of index segments is affected by the page size of the data file. See Btrieve API Guide for more information on how to use the PAGESIZE keyword. The maximum number of indexes you can create for a table depends on the page size of its data file and the number of segments in each index. As the following table shows, data files with page sizes smaller than 4096 bytes cannot contain as many index segments as a data file with a page size of 4096. The number of index segments that you may use depends on the file’s page size.

See also the status codes 26: The number of keys specified is invalid and 29: The key length is invalid, both in Status Codes and Messages.

Using the page size and fixed record length, you can calculate the efficiency with which data is being stored (such as the number of wasted bytes per page). By having fewer records per page, you can improve concurrency where page-level locking is concerned.

By default, PSQL creates all tables with a page size of 4096 bytes. However, you can specify a different page size using the PAGESIZE keyword in a CREATE TABLE statement, or you can create a table using the MicroKernel Database Engine and specify a different page size for that table.

When calculating the total number of index segments defined for a table, a nonsegmented index counts as one index segment. For example, if your table has three indexes defined, one of which has two segments, the total number of index segments is four.

You can use the PSQL Control Center to display the number of defined index segments and the page size of a data file. For information about this utility, see PSQL User's Guide.

When you create an index, you can assign to it a set of qualities, or attributes. Index attributes determine the modifiability of the index and how PSQL sorts the indexes you define for a table. You can include parameters specifying index attributes anytime you create or alter an index definition.

Indexes can have the following attributes:

Uniqueness and modifiability apply only to entire indexes. You cannot apply uniqueness or modifiability to a single index segment without applying it to the entire index. For example, if you create a segmented index and specify the MOD keyword for one of the index segments, you must specify the MOD keyword for every segment.

In contrast, you can apply case-sensitivity, sort order, and segmentation to individual index segments without affecting the entire index. For example, you can create a case-insensitive index segment in an otherwise case-sensitive index.

Partial Indexes apply only to the last column defined in the index, as long as that column meets the following criteria:

For more information on indexes and their attributes, see CREATE INDEX in SQL Engine Reference.