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

You can create and modify tables, constraints, indexes and relationships in Access by writing data-definition queries in SQL view. This article explains data-definition queries and how to use them to create tables, constraints, indexes, and relationships. This article can also help you to decide when to use a data-definition query.

Overview

Unlike other Access queries, a data-definition query does not retrieve data. Instead, a data-definition query uses Data Definition Language to create, modify, or delete database objects.

Note: Data Definition Language (DDL) is part of Structured Query Language (SQL).

Data-definition queries can be very convenient. You can regularly delete and re-create parts of your database schema merely by running some queries. Consider using a data-definition query if you are familiar with SQL statements and you plan to delete and re-create particular tables, constraints, indexes or relationships.

Warning: 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.

Important: Make a backup copy of any tables involved before you run a data-definition query.

DDL keywords

Keyword	Use
CREATE	Create an index or table that does not already exist.
ALTER	Modify an existing table or column.
DROP	Delete an existing table, column, or constraint.
ADD	Add a column or a constraint to a table.
COLUMN	Use with ADD, ALTER, or DROP
CONSTRAINT	Use with ADD, ALTER, or DROP
INDEX	Use with CREATE
TABLE	Use with ALTER, CREATE, or DROP

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 and the name of the table, but usually you will want to define some fields or other aspects of the table. Consider this 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 might first need to enable the contents of the database in order for a data definition query to run:

On the Message Bar, click Enable Content.

Create a table

On the Create tab, in the Macros & Code group, click Query Design.
Close the Show Table dialog box.
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.
Type the following SQL statement:

CREATE TABLE Cars (Name TEXT(30), Year TEXT(4), Price CURRENCY)
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 one 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:

On the Create tab, in the Macros & Code group, click Query Design.
Close the Show Table dialog box.
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.
Type the following SQL statement:

ALTER TABLE Cars ADD COLUMN Condition TEXT(10)
On the Design tab, in the Results group, click Run.

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.

The DESC argument causes the index to be created in descending order, which can be useful if you frequently run queries that look for top values for the indexed field, or that sort the indexed field in descending order. By default, an index is created in ascending order.
The WITH PRIMARY argument establishes the indexed field or fields as the primary key of the table.
The WITH DISALLOW NULL argument causes the index to require that a value be entered for the indexed field — that is, null values are not allowed.

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:

On the Create tab, in the Macros & Code group, click Query Design.
Close the Show Table dialog box.
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.
Type the following SQL statement:

CREATE INDEX YearIndex ON Cars (Year)
On the Design tab, in the Results group, click Run.

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. You do not use a special keyword to indicate that a constraint is a relationship.

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:

On the Create tab, in the Macros & Code group, click Query Design.
Close the Show Table dialog box.
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.
Type the following SQL statement:

ALTER TABLE Cars ALTER COLUMN Condition TEXT CONSTRAINT ConditionRequired NOT NULL
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.

Note: If you want to follow along with the remaining procedures, add some fake data to the Cars table that you created in preceding steps.

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 the values that you want to use for the condition of cars:

On the Create tab, in the Macros & Code group, click Query Design.
Close the Show Table dialog box.
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.
Type the following SQL statement:

CREATE TABLE CarCondition (Condition TEXT(10))
On the Design tab, in the Results group, click Run.
Create a primary key for the table by using an ALTER TABLE statement:

ALTER TABLE CarCondition ALTER COLUMN Condition TEXT CONSTRAINT CarConditionPK PRIMARY KEY
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. Unlike a data-definition query, an append query ends with a semicolon.
On the Design tab, in the Results group, click Run.

Create a relationship by using a constraint

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:

On the Create tab, in the Macros & Code group, click Query Design.
Close the Show Table dialog box.
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.
Type the following SQL statement:

ALTER TABLE Cars ALTER COLUMN Condition TEXT CONSTRAINT FKeyCondition REFERENCES CarCondition (Condition)
On the Design tab, in the Results group, click Run.

Multiple-field constraints

A multiple-field CONSTRAINT clause can be used only outside 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}]}

Consider another example that uses the Cars table. Suppose that you want to make sure 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:

On the Create tab, in the Macros & Code group, click Query Design.
Close the Show Table dialog box.
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.
Type the following SQL statement:

ALTER TABLE Cars ADD CONSTRAINT NoDupes UNIQUE (name, year, condition, price)
On the Design tab, in the Results group, click Run.

Top of Page

Microsoft Office Tutorials

Monday, January 14, 2019