Alison Balteramp;#039;s Mastering Microsoft Office Access 1002003 [Electronic resources] نسخه متنی

Using SQL for Data Definition

Access 2003 offers two methods of programmatically defining and modifying objects. You can use either ActiveX Data Object Extensions for DDL and Security (ADOX) or Data Definition Language (DDL). DDL is covered in this chapter. ADOX is covered in Chapter 14.

The CREATE TABLE Statement

As its name implies, the CREATE TABLE statement is used to create a new table. The syntax is

CREATE TABLE

table-name
(column1 type1 [(size1)] [CONSTRAINT column-constraint1]
[,column2 type2 [(size2)] [CONSTRAINT column-constraint2]
[,...]]
[CONSTRAINT table-constraint1 [,table-constraint2 [,]]])

You must designate the type of data for each column included in the table. When defining a text field, you can also specify the size parameter. Notice that constraints are available at the table level and at the field level. Here's an example of a CREATE TABLE statement:

CREATE TABLE tblCustomers
(CustomerID LONG, CompanyName TEXT (50), IntroDate DATETIME)

This example creates a table named tblCustomers. The table will contain three fields: CustomerID (Long), CompanyName (Text), and IntroDate (DateTime).

The CONSTRAINT clause allows you to create primary and foreign keys. It looks like this:

CONSTRAINT

name {PRIMARY KEY|UNIQUE|REFERENCES

foreign-table [

foreign-column ]}

Here's an example:

CREATE TABLE tblCustomers
(CustomerID LONG CONSTRAINT CustomerID PRIMARY KEY,
CompanyName TEXT (50), IntroDate DATETIME)

The example creates a primary key index based on the CustomerID field.

The CREATE INDEX Statement

The CREATE INDEX statement is used to add an index to an existing table. It is supported in Access, but is not part of the ANSI standard. It looks like this:

CREATE [UNIQUE] INDEX

index-name
ON

table-name (

column1 [

,column2 [,...]])
[WITH {PRIMARY|DISALLOW NULL|IGNORE NULL}]

Here's an example:

CREATE INDEX CompanyName
ON tblCustomers (CompanyName)

The example creates an index called CompanyName, based on the CompanyName field.

The ALTER TABLE Statement

The ALTER TABLE statement is used to modify the structure of an existing table. The syntax has four forms. The first form looks like this:

ALTER TABLE

table-name ADD [COLUMN]

column-name datatype [(

size )]
[CONSTRAINT column-constraint]

This form of the ALTER TABLE statement adds a column to an existing table. Here's an example:

ALTER TABLE tblCustomers ADD ContactName Text 50

The second form uses the following syntax to delete a column from an existing table:

ALTER TABLE

table-name DROP [COLUMN]

column-name

Here's an example:

ALTER TABLE tblCustomers DROP COLUMN ContactName

The third form uses the ALTER TABLE statement to add a constraint to an existing column. The syntax is

ALTER TABLE

table-name ADD CONSTRAINT

constraint

Here's an example:

ALTER TABLE tblCustomers ADD CONSTRAINT CompanyName UNIQUE (CompanyName)

Finally, the fourth form drops a constraint from an existing column:

ALTER TABLE

table-name DROP CONSTRAINT

index

Here's an example:

ALTER TABLE tblCustomers DROP CONSTRAINT CompanyName

The DROP INDEX Statement

The DROP INDEX statement is used to remove an index from a table. The syntax is as follows:

DROP INDEX

index ON

table-name

Here's an example:

DROP INDEX CompanyName ON tblCustomers

The DROP TABLE Statement

The DROP TABLE statement is used to remove a table from the database. The syntax is

DROP TABLE

table-name

Here's an example:

DROP TABLE tblCustomers