Create or modify tables or indexes by using a data-definition query

In Microsoft Office Access 2007, it is easy to create and modify tables, constraints (constraint: A restriction placed on the value that can be entered into a column or a row. For example, values in the Age column cannot be less than 0 or greater than 110.), and indexes (index: A feature that speeds up searching and sorting in a table based on key values and can enforce uniqueness on the rows in a table. The primary key of a table is automatically indexed. Some fields can't be indexed because of their data type.) in Design view, and to create relationships (relationship: An association that is established between common fields (columns) in two tables. A relationship can be one-to-one, one-to-many, or many-to-many.) by using the Relationships window (Relationships window: An object tab in which you view, create, and modify relationships between tables and queries.). Alternatively, you can create and modify these same entities by writing data-definition queries in SQL view.

This article describes data-definition queries, and demonstrates how to use such queries to create tables, indexes, constraints, and relationships. This article can also help you to decide when to use a data-definition query.

What do you want to do?


Learn when to use a data-definition query

Unlike other queries, a data-definition query does not retrieve data. Most queries use Data Manipulation Language (DML), which comprises Structured Query Language (SQL) commands that specify how data from existing database objects is combined and manipulated to produce the data results that you want. Data-definition queries use Data Definition Language (DDL), which comprises SQL commands that specify the definition of database objects that store or index data, and SQL commands that control user access to database objects. In Office Access 2007, a data-definition query defines or changes the definition of a database object.

In general, you should use the graphical design interfaces that Office Access 2007 provides (for example, Design view and the Relationships window) to create tables, constraints, indexes, and relationships. However, if you have tables that you regularly delete and re-create, using these interfaces might become time consuming. By using a data-definition query, you can write a query once, and then run it again when necessary. Consider using a data-definition query only if you are familiar with SQL statements and if you plan to delete and re-create particular tables, constraints, or indexes regularly.

 Important   Using data-definition queries to modify database objects can be risky, because the actions are not accompanied by confirmation dialog boxes. If you make a mistake, you can lose data or inadvertently change the design of a table. Be careful when you use a data-definition query to modify objects in your database. If you are not responsible for maintaining the database that you are using, you should consult with the administrator of the database before running a data-definition query. In addition, it is a good practice to make a backup copy of any tables on which you are about to run such a query.

Top of Page Top of Page

Create or modify a table

To create a table, you use a CREATE TABLE command. A CREATE TABLE command has the following syntax:

CREATE TABLE table_name
 (field1 type [(size)] [NOT NULL] [index1]
 [, field2 type [(size)] [NOT NULL] [index2]
 [, ...][, CONSTRAINT constraint1 [, ...]])

The only required elements of a CREATE TABLE command are the CREATE TABLE command itself, the name of the table, at least one field, and the data type of each field. Let us look at a simple example.

Suppose that you want to create a table to store the name, year, and the price of used cars that you are considering for purchase. You want to allow up to 30 characters for the name, and 4 characters for the year. To use a data-definition query to create the table, do the following:

 Note   You must first enable the contents of the database in order for a data definition query to run.

  1. On the Message Bar, click Options, and then click Enable this content.
  2. On the Create tab, in the Other group, click Query Design.
  1. Close the Show Table dialog box.
  2. On the Design tab, in the Query Type group, click Data Definition.

The design grid is hidden, and the SQL view object tab is displayed.

  1. Type the following SQL statement:

CREATE TABLE Cars (Name TEXT(30), Year TEXT(4), Price CURRENCY)

  1. On the Design tab, in the Results group, click Run.

Modify a table

To modify a table, you use an ALTER TABLE command. You can use an ALTER TABLE command to add, modify, or drop (remove) columns or constraints. An ALTER TABLE command has the following syntax:

ALTER TABLE table_name predicate

where predicate can be any of the following:

ADD COLUMN field type[(size)] [NOT NULL] [CONSTRAINT constraint]
ADD CONSTRAINT multifield_constraint
ALTER COLUMN field type[(size)]
DROP COLUMN field
DROP CONSTRAINT constraint

Suppose that you want to add a 10-character text field to store information about the condition of each car. You can do the following:

  1. On the Create tab, in the Other group, click Query Design.
  1. Close the Show Table dialog box.
  2. On the Design tab, in the Query Type group, click Data Definition.

The design grid is hidden, and the SQL view object tab is displayed.

  1. Type the following SQL statement:

ALTER TABLE Cars ADD COLUMN Condition TEXT(10)

  1. On the Design tab, in the Results group, click Run.

Top of Page Top of Page

Create an index

To create an index on an existing table, you use a CREATE INDEX command. A CREATE INDEX command has the following syntax:

CREATE [UNIQUE] INDEX index_name
 ON table (field1 [DESC][, field2 [DESC], ...])
 [WITH {PRIMARY | DISALLOW NULL | IGNORE NULL}]

The only required elements are the CREATE INDEX command, the name of the index, the ON argument, the name of the table that contains the fields that you want to index, and the list of fields to be included in the index.

Suppose that you have a table named Cars with fields that store the name, year, price, and condition of used cars that you are considering for purchase. Also suppose that the table has become large and that you frequently include the year field in queries. You can create an index on the Year field to help your queries return results more quickly by using the following procedure:

  1. On the Create tab, in the Other group, click Query Design.
  1. Close the Show Table dialog box.
  2. On the Design tab, in the Query Type group, click Data Definition.

The design grid is hidden, and the SQL view object tab is displayed.

  1. Type the following SQL statement:

CREATE INDEX YearIndex ON Cars (Year)

  1. On the Design tab, in the Results group, click Run.

Top of Page Top of Page

Create a constraint or a relationship

A constraint establishes a logical condition that a field or combination of fields must meet when values are inserted. For example, a UNIQUE constraint prevents the constrained field from accepting a value that would duplicate an existing value for the field.

A relationship is a type of constraint that refers to the values of a field or combination of fields in another table to determine whether a value can be inserted in the constrained field or combination of fields.

To create a constraint, you use a CONSTRAINT clause in a CREATE TABLE or ALTER TABLE command. There are two kinds of CONSTRAINT clauses: one for creating a constraint on a single field, and another for creating a constraint on multiple fields.

Single-field constraints

A single-field CONSTRAINT clause immediately follows the definition of the field that it constrains, and has the following syntax:

CONSTRAINT constraint_name {PRIMARY KEY | UNIQUE | NOT NULL |
 REFERENCES foreign_table [(foreign_field)]
 [ON UPDATE {CASCADE | SET NULL}]
 [ON DELETE {CASCADE | SET NULL}]}

Suppose that you have a table named Cars with fields that store the name, year, price, and condition of used cars that you are considering for purchase. Also suppose that you frequently forget to input a value for the car's condition, and that you always want to record this information. You can create a constraint on the Condition field that prevents you from leaving the field empty, by using the following procedure:

  1. On the Create tab, in the Other group, click Query Design.
  1. Close the Show Table dialog box.
  2. On the Design tab, in the Query Type group, click Data Definition.

The design grid is hidden, and the SQL view object tab is displayed.

  1. Type the following SQL statement:

ALTER TABLE Cars ALTER COLUMN Condition TEXT CONSTRAINT ConditionRequired NOT NULL

  1. On the Design tab, in the Results group, click Run.

Now suppose that, after a while, you notice that there are many similar values in the Condition field that should be the same. For example, some of the cars have a Condition value of poor and others have a value of bad. After you clean up the values so that they are more consistent, you could create a table, named CarCondition, with one field, named Condition, that contains all of the values that you want to use for the condition of cars:

  1. On the Create tab, in the Other group, click Query Design.
  1. Close the Show Table dialog box.
  2. On the Design tab, in the Query Type group, click Data Definition.

The design grid is hidden, and the SQL view object tab is displayed.

  1. Type the following SQL statement:

CREATE TABLE CarCondition (Condition TEXT(10))

  1. On the Design tab, in the Results group, click Run.
  1. To insert the values from the Condition field of the Cars table into the new CarCondition table, type the following SQL into the SQL view object tab:

INSERT INTO CarCondition SELECT DISTINCT Condition FROM Cars;

 Note   The SQL statement in this step is an append query (append query: An action query that adds the records in a query's result set to the end of an existing table.). Unlike a data-definition query, an append query ends with a semicolon.

  1. On the Design tab, in the Results group, click Run.

To require that any new value inserted in the Condition field of the Cars table matches a value of the Condition field in the CarCondition table, you can then create a relationship between CarCondition and Cars on the field named Condition, by using the following procedure:

  1. On the Create tab, in the Other group, click Query Design.
  1. Close the Show Table dialog box.
  2. On the Design tab, in the Query Type group, click Data Definition.

The design grid is hidden, and the SQL view object tab is displayed.

  1. Type the following SQL statement:

ALTER TABLE Cars ALTER COLUMN Condition TEXT CONSTRAINT FKeyCondition REFERENCES CarCondition (Condition)

  1. On the Design tab, in the Results group, click Run.

Multiple-field constraints

A multiple-field CONSTRAINT clause can be used only outside of a field-definition clause, and has the following syntax:

CONSTRAINT constraint_name 
{PRIMARY KEY (pk_field1[, pk_field2[, ...]]) |
 UNIQUE (unique1[, unique2[, ...]]) |
 NOT NULL (notnull1[, notnull2[, ...]]) |
 FOREIGN KEY [NO INDEX] (ref_field1[, ref_field2[, ...]]) 
  REFERENCES foreign_table 
  [(fk_field1[, fk_field2[, ...]])] |
 [ON UPDATE {CASCADE | SET NULL}]
 [ON DELETE {CASCADE | SET NULL}]}

Let us look at another example using the Cars table. Suppose that you want to ensure that no two records in the Cars table have the same set of values for Name, Year, Condition, and Price. You could create a UNIQUE constraint that applies to these fields, by using the following procedure:

  1. On the Create tab, in the Other group, click Query Design.
  1. Close the Show Table dialog box.
  2. On the Design tab, in the Query Type group, click Data Definition.

The design grid is hidden, and the SQL view object tab is displayed.

  1. Type the following SQL statement:

ALTER TABLE Cars ADD CONSTRAINT NoDupes UNIQUE (name, year, condition, price)

  1. On the Design tab, in the Results group, click Run.

Top of Page Top of Page

 
 
Applies to:
Access 2007