Create Table

  • ALTER TABLE
  • DROP TABLE
  • CREATEINDEX
  • DROP INDEX

The DDL language set is more concise than the Data Manipulation Language but no less important. In fact, outside of database tools that take all of their direction through SQL statements, these statements are little used by programmers. Many programmers, not surprisingly, prefer to use interactive tools to create and manage their data objects. After this

Chapter 3-Structured Query Language review, you might see these commands in a new light and realize opportunities for expanding your application development skill set.

CREATE TABLE Statement

CREATE TABLE table name

( column-definition [, column-definition, . . ] [, primary-key-constraint ]

The CREATE TABLE statement is used to create a new, empty table, defining its columns and setting a primary key for relation. Local SQL supports the creation of dBASE and Paradox tables through this statement. The table and column names must meet all of the requirements defined for these identifiers, and if they contain embedded spaces, each must be surrounded by quotes.

A column definition is composed of a column name, a data type, and a size, if applicable. There is no punctuation between these elements, but multiple column definitions are comma separated. Though the native BDE databases support a wide range of data types in their proprietary tables, Local SQL supports a common subset as shown in Figure 3.4.

Chapter 3-Structured Query Language

Column Type

SMALLINT

Syntax Definition

No scale or precision specified.

Column Type

SMALLINT

Syntax Definition

No scale or precision specified.

DATE

No scale or precision specified.

DATE

No scale or precision specified.

No scale or precision specified.

Part I -The Relational Database

Column Type

BLOB (length, type)

Syntax Definition

Specify length of column capacity, in bytes. Specify the type of BLOB column: Memo (I), Binary (2), Formatted Memo (3), OLE (4), or Graphic/Binary (5). The length of a Paradox BLOB column must be between 0 and 240. This represents the amount of BLOB data that is stored in the .DB file itself; for dBASE tables the length must be between 0 and

Figure 3.4 Column types supported by Local SQL.

The following SQL statement creates a Paradox table called STUDENT:

CREATE TABLE1 student .db '

LastName Fi rstName

Major GPA

CHAR CHAR CHAR

NUMERIC

PRIMARY KEY (LastName, FirstName))

The PRIMARY KEY keyword defines a primary index for the relation composed of the items contained in the column list, in this case LastName plus FirstName.

The type of table that is created is defined by the filename extension that is a part of the table name. ".DB" will build a Paradox table; ".DBF" builds dBASE tables. If the extension is omitted in the table name, the default driver setting in the BDE is referenced for the type of table to build.

The ALTER TABLE Statement

ALTER TABLE table-name

DROP [COLUMN] col umn_reference[, col umn-reference, . .] ADD [COLUMN] col umn-reference [,COl umn-reference, . .]

Part I -The Relational Database

The CREATE INDEX Statement

ALTER TABLE 'student.db1 DROP gpa

ALTER TABLE 'student.db'

DROP FirstName, ADD Firstlnitial CHAR (l)

DROP TABLE tab le-name

DROP TABLE is simple enough to understand and use. Its purpose is to delete an existing table from a database. No questions asked. No warning. No caution dialog. You get the idea.

CREATE (UNIQUE] [ASC | DESC] INDEX index-name ON table-name (column name [.column name,...])

CREATE INDEX DESC students ON Student (LastName,Firstlnitial)

You use the ALTER TABLE SQL statement to restructure a table in a database. The ALTER TABLE statement can either add new columns to a table or delete an existing column. Both of these actions can be combined into a single ALTER TABLE statement.

To delete a column from a table, you only need to provide the name of the column. The following statement deletes the GPA column from the STUDENT table:

ALTER TABLE 'student.db1 DROP gpa

When adding a column, the column definition must follow the same structure described in the CREATE TABLE section. The statement below drops the column FirstName from the STUDENT relation and replaces it with a new column named Firstlnitial.

ALTER TABLE 'student.db'

DROP FirstName, ADD Firstlnitial CHAR (l)

It is important to note that if a dropped column is a part of the primary key for the table, the primary index will be deleted. You should then rebuild the primary index before going forward based on a new or existing column.

The DROP TABLE Statement

DROP TABLE tab le-name

DROP TABLE is simple enough to understand and use. Its purpose is to delete an existing table from a database. No questions asked. No warning. No caution dialog. You get the idea.

The CREATE INDEX Statement

CREATE (UNIQUE] [ASC | DESC] INDEX index-name ON table-name (column name [.column name,...])

The CREATE INDEX statement is used to build secondary indexes for existing Paradox and dBASE tables. The value used for the index name may not contain embedded spaces. Paradox indexes may be composed of multiple columns but dBASE indexes developed through the SQL statement are limited to using a single column. When the UNIQUE keyword is used, the index will raise an exception if duplicate values are encountered. The statement:

CREATE INDEX DESC students ON Student (LastName,Firstlnitial)

Part I -The Relational Database creates a Paradox index built with the LastName and Firstinitial columns of the STUDENTS table. The keyword DESC is used to sort the index in descending order.

Was this article helpful?

0 0

Post a comment