Local SQL Part 8: Creating and Restructuring Tables

Written by Larry DiGiovanni

26 April 2002

Learn how to create, delete, restructure tables and how to add and remove indices.Local SQL Part 8
Creating and Restructuring Tables
© 2001 Lawrence G. DiGiovanni
Digico, Inc.
www.digicoinc.com

Preface

Local SQL is a very powerful yet commonly underutilized tool that is provided with Paradox (as part of the Borland Database Engine). It exposes the power and flexibility of the SQL sublanguage to both Paradox developers as well as interactive end-users.

This is the eighth in a planned series of articles on using Local SQL with Paradox/BDE applications. This article discusses how to create and restructure tables using SQL statements instead of procedural code.

Introduction

One of the biggest differences between Local SQL and QBE is that Local SQL includes the capability for creating and restructuring tables, unlike QBE. With Local SQL you can:

Create Tables
Delete Tables
Restructure tables (Add and Delete columns)
Index tables
Delete Indexes
Define Primary Keys

We address each of the above operations with examples in the below sections.

CREATE TABLE

The CREATE TABLE statement allows you to create a new table. The form of the CREATE TABLE statement is (from LocalSQL.HLP)

CREATE TABLE table_reference
   (column_definition [, column_definition,...]
[, primary_key_constraint])

So if we wanted to create a table orders.db with the following structure:

orders.db
orderid     I*
orderco     A20
orderdate   D
ordertotal  $
ordernotes  M10

We'd issue a Local SQL CREATE TABLE statement as follows:

CREATE TABLE orders (
   orderid    INTEGER,
   orderco    CHAR(20),
   orderdate  DATE,
   ordertotal MONEY,
   ordernotes BLOB(10, 1),
   PRIMARY KEY (orderid)
)

Note that the column types are different in Local SQL than they appear in the create/restructure table dialog in Paradox. A complete list of the column types can be found in LocalSQL.HLP.

RESTRUCTURE TABLE

Now let's say we have our orders table, but we forgot to include the shipping address, and we want to use the company ID instead of the company name. So we need to restructure our table from the above structure to one like this:

orders.db
orderid     I*
ordercoid   I
orderaddr1  A40
orderaddr2  A40
ordercity   A30
orderstate  A2
orderzip    A5
orderdate   D
ordertotal  $
ordernotes  M10

We'd issue a Local SQL ALTER TABLE statement as follows:

ALTER TABLE orders
   DROP orderco,
   ADD ordercoid INTEGER,
   ADD orderaddr1 CHAR(40),
   ADD orderaddr2 CHAR(40),
   ADD ordercity CHAR(30),
   ADD orderstate CHAR(2),
   ADD orderzip CHAR(5)

Note that the new columns are actually added at the end of the field roster. Local SQL doesn't allow you to add columns in the middle of the field roster.

CREATE INDEX

Now let's say we want to add a secondary index to our orders table on ordercoid. We'd issue a Local SQL CREATE INDEX statement as follows:

CREATE INDEX ordercoid_idx ON orders (ordercoid)

We now have an ascending, maintained, non-case-sensitive index on ordercoid. If we wanted the index to be unique and descending, we could've used the UNIQUE and DESC modifier, as in:

CREATE UNIQUE DESC INDEX ordercoid_idx ON orders (ordercoid)

Indexes created using Local SQL CREATE INDEX are not case sensitive. Local SQL does allow the creation of a non-case sensitive index with the same name as a table column name, which is not allowed using the restructure dialog. This is probably a capability that should not be used as results might be unpredictable.

REMOVE INDEX

Now let's say we want to get rid of that secondary index we created above. We'd issue a Local SQL DROP INDEX statement as follows:

DROP INDEX orders.ordercoid

DELETE TABLE

To delete a table and all of it's family members, use the SQL DROP TABLE command. To drop the orders table we created earlier, and all of it's indexes, etc., we'd issue a Local SQL DROP TABLE command as follows:

DROP TABLE orders

Unsupported Capabilities

There are a number of table modifications which cannot be performed using Local SQL, such as foreign key constraints, range limitations, picture settings, etc. To perform these types of modifications requires ObjectPAL or the Paradox restructure dialog.

Note that these are limitations of Local SQL only, which is a subset of ANSI 92 SQL. Most SQL dialects do support the above operations using SQL syntax.

Summary

Local SQL allows a user or developer to create, restructure and reindex Paradox tables using SQL syntax. This capability provides an alternative to ObjectPAL or the Paradox restructure dialog for certain types of table definitions and modifications.

Local SQL Part 9

< Prev		Next >

[ Back ]