Data Definition Statements

---

Data definition statements let you specify the characteristics of your database. When you execute data definition statements, Pervasive.SQL stores the description of your database in a data dictionary. You must define your database in the dictionary before you can store or retrieve information.

Pervasive.SQL allows you to construct data definition statements to do the following:

• Create, modify, and delete tables.
• Create and delete indexes.
• Create and delete triggers.
• Create and delete stored procedures.

The following sections briefly describe the SQL statements associated with each of these tasks. For general information about defining the characteristics of your database, refer to the Pervasive.SQL Programmer's Guide, available in the Pervasive.SQL Software Developer Kit (SDK).

Creating, Modifying, and Deleting Tables

You can create, modify, and delete tables from a database by constructing SQL using the following statements:

 
Table 1-2 Data Definition Statements - Tables

CREATE TABLE	Defines a table and optionally creates the corresponding data file.
ALTER TABLE	Changes a table definition. With an ALTER TABLE statement, you can perform such actions as add a column to the table definition, remove a column from the table definition, change a column's data type or length (or other characteristics), and add or remove a primary key or a foreign key and associate the table definition with an different data file.
DROP TABLE	Deletes a table from the data dictionary and optionally deletes the associated data file from the disk.

Creating and Deleting Indexes

You can create and delete indexes from a database by constructing SQL s using the following statements:

 
Table 1-3 Data Definition Statements - Indexes

CREATE INDEX	Defines a new index (a named index) for an existing table.
DROP INDEX	Deletes a named index.

Creating and Deleting Triggers

You can create and delete triggers from a database by constructing SQL using the following statements:

 
Table 1-4 Data Definition Statements - Triggers

CREATE TRIGGER	Defines a trigger for an existing table.
DROP TRIGGER	Deletes a trigger.

Pervasive.SQL provides additional SQL control statements, which you can only use in the body of a trigger. You can use the following statements in triggers:

 
Table 1-5 Data Definition Statements - Trigger Control

BEFORE	Defines the trigger execution before the INSERT, UPDATE, or DELETE operation.
AFTER	Defines the trigger execution after the INSERT, UPDATE, or DELETE operation.

Creating and Deleting Stored Procedures

A stored procedure consists of statements you can precompile and save in the dictionary. To create and delete stored procedures, construct statements using the following:

 
Table 1-6 Data Definition Statements - Stored Procedure

CREATE PROCEDURE	Stores a new procedure in the data dictionary.
DROP PROCEDURE	Deletes a stored procedure from the data dictionary.

Pervasive.SQL provides additional SQL control statements, which you can only use in the body of a stored procedure. You can use the following statements in stored procedures:

 
Table 1-7 Data Definition Statements - Stored Procedure Control

IF...THEN...ELSE	Provides conditional execution based on the truth value of a condition.
LEAVE	Continues execution by leaving a block or loop statement.
LOOP	Repeats the execution of a block of statements.
WHILE	Repeats the execution of a block of statements while a specified condition is true.