Working with Data Definition Statements

Go Up to Embedded SQL Guide

This chapter discusses how to create, modify, and delete databases, tables, views, and indexes in SQL applications. Tables of a database, views, and indexes make up most of its underlying structure, or metadata.

Important: The discussion in this chapter applies equally to dynamic SQL (DSQL) applications, except that users enter DSQL data definition statements at run time, and do not preface those statements with EXEC SQL.

The preferred method for creating, modifying, and deleting metadata is through the InterBase interactive SQL tool, isql, but in some instances, it may be necessary or desirable to embed some data definition capabilities in a SQL application. Both SQL and DSQL applications can use the following subset of data definition statements:

CREATE statement	ALTER statement	DROP statement
CREATE DATABASE	ALTER DATABASE	—
CREATE DOMAIN	ALTER DOMAIN	DROP DOMAIN
CREATE GENERATOR	SET GENERATOR	—
CREATE INDEX	ALTER INDEX	DROP INDEX
CREATE SHADOW	ALTER SHADOW	DROP SHADOW
CREATE TABLE	ALTER TABLE	DROP TABLE
CREATE VIEW	—	DROP VIEW
DECLARE EXTERNAL	—	DROP EXTERNAL
DECLARE FILTER	—	DROP FILTER

DSQL also supports creating, altering, and dropping stored procedures, triggers, and exceptions. DSQL is especially powerful for data definition because it enables users to enter any supported data definition statement at run time. For example, isql itself is a DSQL application. For more information about using isql to define stored procedures, triggers, and exceptions, see the Data Definition Guide. For a complete discussion of DSQL programming, see Using Dynamic SQL.

Important: Applications that mix data definition and data manipulation must be preprocessed using the gpre -m switch. Such applications must explicitly start every transaction with SET TRANSACTION.

Topics

• Creating Metadata
• Dropping Metadata
• Altering Metadata