Query in database definition

Create or modify tables or indexes by using a data-definition query
June 19, 2021 – 12:22 pm
Ecora Auditor Help

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.
  3. Close the Show Table dialog box.
  4. On the Design tab, in the Query Type group, click Data Definition.

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

  5. 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 Design tab, in the Query Type group, click Data Definition.

    ALTER TABLE Cars ADD COLUMN Condition TEXT(10)

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:

  1. On the Design tab, in the Query Type group, click Data Definition.

    CREATE INDEX YearIndex ON Cars (Year)

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 Design tab, in the Query Type group, click Data Definition.

    ALTER TABLE Cars ALTER COLUMN Condition TEXT CONSTRAINT ConditionRequired NOT NULL

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 Design tab, in the Query Type group, click Data Definition.

    CREATE TABLE CarCondition (Condition TEXT(10))

  2. 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.

Source: support.office.com
Related Posts