Creating a Data Dictionary

Creating a Data Dictionary

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 41 describes the system tables PSQL creates. See also the chapter System Tables in SQL Engine Reference.

Table 41

PSQL System Tables

Resulting Table

Create a data dictionary

X$File, X$Field, X$Index

Specify column attributes

Create stored SQL procedures

Define database security

X$User, X$Rights

Define referential constraints

Define triggers

X$Trigger, X$Depend

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.

Note: PSQL does not display some data in the system tables. For example, information about stored views and procedures, other than their names, is available only to PSQL. In addition, some data (such as user passwords) displays in encrypted form.

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

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

►

To create a named database, perform the following steps:

Note: You must have named databases in order to use some features, such as referential integrity and triggers.

1

Create a directory in which to store the new dictionary tables.

2

Use the PSQL Control Center to add a Named Database. See To create a new database in PSQL User's Guide.

►

To create a dictionary for an unnamed database, perform the following steps:

1

Run PSQL DDF Builder.

2

Review the information in DDF Builder User’s Guide for creating data dictionary files (DDFs). See Getting Started with DDF Builder.