![]() |
![]() |
|
![]() |
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 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 M10We'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 M10We'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 Discussion of this article |
![]() Feedback | Paradox Day | Who Uses Paradox | I Use Paradox | Downloads ![]() |
|
![]() The information provided on this Web site is not in any way sponsored or endorsed by Corel Corporation. Paradox is a registered trademark of Corel Corporation. ![]() |
|
![]() Modified: 15 May 2003 Terms of Use / Legal Disclaimer ![]() |
![]() Copyright © 2001- 2003 Paradox Community. All rights reserved. Company and product names are trademarks or registered trademarks of their respective companies. Authors hold the copyrights to their own works. Please contact the author of any article for details. ![]() |
![]() |
|