Advantage Database Server: The Official Guide

Which came first, the table or the data? The data, of course, which is why you need the table in the first place. But seriously, you can’t have a database without one or more tables. Furthermore, tables are not much use if you do not have indexes for them. After that, views, stored procedures, triggers, and the like prove quite valuable.

This section covers three essential SQL statements: CREATE, ALTER, and DROP. You use CREATE to create tables and data dictionary objects, ALTER to modify the structure of an existing table, and DROP to destroy objects. Each of these statements is covered in the following sections.

Creating Tables and Indexes

You use the CREATE statement to create data dictionaries, tables, indexes, and views, and to register AEPs (Advantage Extended Procedures) and triggers. Creating tables and indexes is covered in the following sections.

Creating Tables

You create a table using the CREATE TABLE SQL statement. If your ADS connection is for a data dictionary, you must connect using the data dictionary administrator’s user name (ADSSYS) and password. The created table will be bound to the data dictionary. If you are connected using a user account, or are not connected to a data dictionary, CREATE TABLE creates a free table.

When calling CREATE TABLE, you must supply a name for the table as well as the name and type of each field in the table, at a minimum. For example, the following statement creates a table named DEMO1 with four fields:

CREATE TABLE DEMO1 ( "Full Name" CHAR(30), "Date of Birth" DATE, "Credit Limit" MONEY, Active LOGICAL)

The names that you use in SQL for the valid field types for ADT tables are shown in Table 10-1. For the valid field types for DBF tables, refer to the ADS help.

Almost all tables will have at least one index, which is typically a unique, primary index. There are two ways to define a primary index. If the index is based on a single field, you can include the keywords PRIMARY KEY following the field type.

Alternatively, you can follow the field list with the keywords PRIMARY KEY followed by a list of one or more fields that will constitute the index key expression. If you want the index to sort in descending order, include the DESC keyword after each descending field.

The following CREATE TABLE statement creates a table named DEMO2 that includes a primary index named PrimeIdx:

CREATE TABLE DEMO2 ( CustID INTEGER CONSTRAINT PrimeIdx PRIMARY KEY, [Full Name] CHAR(25), Date DATE)

The following statement performs the exact same task:

CREATE TABLE DEMO2 ( CustID INTEGER, [Full Name] CHAR(25), Date DATE, CONSTRAINT PrimeIdx PRIMARY KEY (CustID))

In both of these statements, the CONSTRAINT PrimeIdx can be omitted, in which case ADS will assign the name PK_INDEX to the primary key index.

You can also define field-level constraints when creating a table. This is demonstrated in the following CREATE TABLE statement:

CREATE TABLE DEMO3 ( "Customer ID" INTEGER PRIMARY KEY, "Credit Limit" MONEY DEFAULT '0' CONSTRAINT MINIMUM '0' CONSTRAINT MAXIMUM '100000' CONSTRAINT NOT NULL CONSTRAINT ERROR MESSAGE 'Invalid credit limit', "Date Last Accessed" DATE)

Using the SELECT statement, you can both create a table and populate it with data from an existing table. To do this, follow the SELECT list with the name of the table into which you want to place the selected records. For example, the following statement will create a new table based on the EMPLOYEE table. The resulting table has two fields, First Name and Last Name. This statement creates a free table and its files are written to the same path as the data dictionary in which the EMPLOYEE table is bound.

SELECT "First Name", "Last Name" INTO DEMO4 FROM EMPLOYEE

Creating Indexes

You create an index for a table using the CREATE INDEX SQL statement. If you are creating an index for a table in a data dictionary, you must be connected to the data dictionary using the administrative account (otherwise, the index will be temporary).

The following SQL script creates a new table, and then uses two CREATE INDEX statements to create indexes for it:

CREATE TABLE DEMO5 ( CustID INTEGER, "Full Name" CHAR(30), Address CHAR(100), City CHAR(25), Phone CHAR(14), Notes MEMO); CREATE UNIQUE INDEX UniqueIdx ON DEMO5 (CustID); CREATE INDEX AddrIdx ON DEMO5 (Address, City);

Neither of these CREATE INDEX statements specifies an index file name. As a result, these index orders are created in the structural index file. If you want, you can create a new index file, in which case you can specify the filename and page size. This is shown in the following example:

CREATE INDEX NewIdx ON DEMO5 ("Full Name") IN FILE "newfile" PAGESIZE 1024

You can also create FTS (full text search) indexes with CREATE INDEX. When creating an FTS index, you use the CONTENT keyword followed by one or more of the following keywords to define how the index is created and maintained: MIN WORD, MAX WORD, DELIMITERS, DROPCHARS, NOISE, CONDITIONALS, NOTMAINTAINED, CASESENSITIVE, KEEPSCORE, and PROTECTNUMBERS. The DELIMITERS, DROPCHARS, NOISE, and CONDITIONALS keywords can be preceded by the keyword NEW to define new values. When you omit the word NEW, the values are added to the default values for those parameters. (See Chapter 3 for a complete discussion of FTS indexes and their parameters.)

The following is an example of an FTS index on the Notes field of the DEMO5 table:

CREATE INDEX NotesIdx ON DEMO5 (Notes) CONTENT MIN WORD 3 MAX WORD 15 DELIMITERS ';:' KEEPSCORE CASESENSITIVE

Altering Tables

The ALTER TABLE statement can be used to change an existing table’s definition. It permits you to add and remove fields from the table’s structure, add or remove a primary index, and change field names and data types, as well as add, change, or drop field-level constraints.

If your table is bound to a data dictionary, you can only alter a table if you are connected using the data dictionary administrator’s user name and password. In any case, you must also be able to obtain an exclusive lock on the table in order to alter it. The following is an example of an ALTER TABLE statement:

ALTER TABLE DEMO5 ADD COLUMN State CHAR(40) ADD COLUMN Country CHAR(35) ALTER COLUMN Address Address CHAR(80) DROP COLUMN Phone ALTER COLUMN "Full Name" "Full Name" CHAR(30) CONSTRAINT NOT NULL

Advantage SQL does not support ALTER statements for indexes, views, stored procedures, or triggers. If you want to change one of these objects, you must use DROP to delete the object and then call CREATE to create a new version. Database properties can be modified using special stored procedures. These stored procedures are discussed in Chapter 11.

Dropping Objects

You use SQL DROP statements to destroy tables and objects within your data dictionary. DROP can be used with the following keywords: INDEX, PROCEDURE, TABLE, TRIGGER, and VIEW. For stored procedures, triggers, and views, as well as tables and index orders associated with a data dictionary, you must execute DROP using the data dictionary administrative connection.

To drop an index order, use dot notation to specify the table name and the index order name. For example, the following statement deletes the index named AddrIdx from the DEMO5 table:

DROP INDEX DEMO5.AddrIdx

The following example demonstrates how to destroy the DEMO5 table:

DROP TABLE DEMO5

DROP TABLE removes the specified table from the data dictionary (if it was bound to one) and deletes the files associated with the table. Dropping stored procedures, triggers, and views uses the same syntax as dropping a table. Simply provide the name of the object that you are dropping.