JET Database/Data definition language

Create Table
Tables are created by issuing the  statement. The statement must specify the table name, and any columns in the table.

Drop Table
Tables are dropped by issuing the  statement.

Alter Table
Tables can be altered by issuing one or more  statements. New columns can be added, existing columns can be dropped, and existing columns can be altered

Primary Keys
There are several ways to create primary keys in JET SQL. One can use the  directive in the   statement, as shown below:

The same table, with the same primary key, can be created using the  directive, either as part of the   statement:

or afterwards, in an  statement:

If the table has only one column in its primary key, the constraint can be added to the column specification:

All but the last example, above, support multiple columns in the primary key, e.g.:

Unique Constraints
Unique constraints can be added in the same way, either in the  statement (shown) or with an   statement.

Foreign Key Constraint
A foreign key constraint can be added to a table in the  statement, as show below, or via an   statement.

JET 4.0 introduced cascading updates and deletes to foreign keys. When a foreign key is created with, the foreign keys are updated if the referenced columns are changed. causes the referencing rows to be deleted if the referenced row is deleted, and  sets the foreign keys to Null if the referenced row is deleted.

Check Constraints
Check constraints can be added in much the same way. Note that even though a check constraint may pertain to only one specific column, the constraint is declared at the table level, not the column level:

Indexing
Table indexes help to improve the performance of queries made against a table, including implicit queries within other statements such as updates, deletes, and foreign key verification. Table indexes are created by issuing the  statement.

Indexes can be created with values in each column either ascending or descending, meaning least-first or greatest-first. If not specified, an index will be created with ascending values in each indexed column.

The following statements create a table with two indexes. The first index only covers column b, but the second index covers both columns c and d.

Table indexes can be dropped by issuing the  statement.

Unique indexes
Normally, an index will allow duplicate values. Where each row must have a unique value in the indexed column, or a unique combination of values in the set of columns being indexed, the index can be specified as being unique. This has a similar effect to adding a unique constraint (and is in fact how JET implements a unique constraint). NB: Nulls are not considered values, so if a column in a unique index or unique constraint is allowed to be Null, then multiple rows may have Null in that column.

With Disallow Null
Null handling is generally best specified on the table column. However, the  statement also supports an option to disallow any Null values in the indexed columns.

With Ignore Null
Rows with Nulls in the indexed columns can also be completely excluded from the index, making the index physically smaller on disc and thus faster to search through.

With Primary
The primary key columns of a table can be specified by creating an index with the special  option.

It is generally better to create the primary key with the  constraint directive, unless other options are required when creating the index on the primary key columns. One such example might be when one or more columns in the primary key should be indexed descending rather than ascending, for performance reasons.

Dropping indexes
Dropping an index when it is no longer required is easy too. Specify the index name, and which table the index is on:

Security
When multiple database users, and optionally groups, have been added to the database, restrictions on what those users have access to in the database can be made by granting or revoking privileges on individual objects.

The following basic table privileges from the ANSI SQL standard are supported by JET (the basic "CRUD" privileges – Create, Read, Update, Delete):

In addition, the following table privileges are supported by JET: