Chapter 1: Using the Data Definition Guide
Contents
What is Data Definition?
An InterBase database is created and populated using SQL statements, which can be divided into two major categories: data definition language (DDL) statements and data manipulation language (DML) statements. The underlying structures of the database—its tables, views, and indexes—are created using DDL statements. collectively, the objects defined with DDL statements are known as metadata. Data definition is the process of creating, modifying, and deleting metadata. Conversely, DML statements are used to populate the database with data, and to manipulate existing data stored in the to use DDL statements. For more information on using DML statements, see the Language Reference Guide.
DDL statements that create metadata begin with the keyword CREATE, statements that modify metadata begin with the keyword ALTER, and statements that delete metadata begin with the keyword DROP. Some of the basic data definition tasks include:
- Creating a database (CREATE DATABASE).
- Creating tables (CREATE TABLE).
- Altering tables (ALTER TABLE).
- Dropping tables (DROP TABLE).
InterBase stores database metadata and other information about it in system tables, which are automatically created when you create a database. All system table names begin with “RDB$”. Examples of system tables include RDB$RELATIONS, which has information about each table in the database, and RDB$FIELDS, which has information on the domains in the database. Writing to these system tables without sufficient knowledge can corrupt a database. Therefore, public users can only select from them. The database owner and SYSDBA user have full read and write privileges and can assign these privileges to others if they wish. For more information about the system tables, see the Language Reference Guide.
- Important: If you have permission, you can directly modify columns of a system table, but unless you understand all of the interrelationships between the system tables, modifying them directly can adversely affect other system tables and corrupt your database.
Who Should Use This Guide
The Data Definition Guide is a resource for programmers, database designers, and users who create or change an InterBase database or its elements.
This book assumes the reader has:
- Previous understanding of relational database concepts.
- Read the isql chapter in the InterBase Operations Guide.
Using isql
You can use isql to interactively create, update, and drop metadata, or you can input a file to isql that contains data definition statements, which is then executed by isql without prompting the user. It is usually preferable to use a data definition file because it is easier to modify the file than to retype a series of individual SQL statements, and the file provides a record of the changes made to the database.
The isql interface can be convenient for simple changes to existing data, or for querying the database and displaying the results. You can also use the interactive interface as a learning tool. By creating one or more sample databases, you can quickly become more familiar with InterBase.
Using a Data Definition File
A data definition file can include statements to create, alter, or drop a database, or any other SQL statement. To issue SQL statements through a data definition file, follow these steps:
- Use a text editor to create the data definition file. Each DDL statement should be followed by a COMMIT to insure its visibility to all subsequent DDL statements in the data definition file.
- Save the file.
- Input the file into isql. For information on how to input the data definition file using Windows ISQL, see the Operations Guide. For information on how to input the data definition file using command-line isql, see the Operations Guide.