# JET Database/Data definition language
## Creating and dropping tables
### Create Table

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

### Drop Table

Tables are dropped by issuing the Drop Table statement.

### Alter Table

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

## Constraints
### Primary Keys

There are several ways to create primary keys in JET SQL. One can use the Primary Key directive in the Create Table statement, as shown below:
The same table, with the same primary key, can be created using the Constraint directive, either as part of the Create Table statement:
or afterwards, in an Alter Table 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 Create Table statement (shown) or with an Alter Table statement.

### Foreign Key Constraint

A foreign key constraint can be added to a table in the Create Table statement, as show below, or via an Alter Table statement.
JET 4.0 introduced cascading updates and deletes to foreign keys. When a foreign key is created with Update Cascade, the foreign keys are updated if the referenced columns are changed. Delete Cascade causes the referencing rows to be deleted if the referenced row is deleted, and Delete Set Null 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 Create Index statement.
Indexes can be created with values in each column either ascending (ASC) or descending (DESC), 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 Drop Index 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 Create Index 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 With Primary option.
It is generally better to create the primary key with the Primary Key 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):
| Select | select data from the table |
| Delete | delete data from the table |
| Insert | insert new data into the table |
| Update | update existing data in the table |
In addition, the following table privileges are supported by JET:
| All Privileges | grants or revokes all privileges in one hit |
| Drop | drop the table |
| SelectSecurity | view permissions on the table (i.e. other Grants) |
| UpdateSecurity | update permissions on the table |
| UpdateIdentity | change the values in auto-increment columns |
| SelectSchema | query the design of the table |
| Schema | update the design of the table |
| UpdateOwner | change the owner of the table |