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.

Create Table T (
    a integer,
    b char(10)
)

Drop Table

Tables are dropped by issuing the Drop Table statement.

Drop Table T

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

Alter Table T Add Column c float
go
Alter Table T Drop Column c
go
Alter Table T Alter Column b varchar(20)
go

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:

Create Table P1 (
    i1 int not null,
    c1 varchar(255),
    Primary Key(i1)
)

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

Create Table P2 (
    i1 int not null,
    c1 varchar(255),
    Constraint PK_P2 Primary Key(i1)
)

or afterwards, in an Alter Table statement:

Create Table P3 (
    i1 int not null,
    c1 varchar(255)
)
go
Alter Table P3 Add Constraint PK_P3 Primary Key(i1)
go

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

Create Table P4 (
    i1 int not null Constraint PK_P4 Primary Key,
    c1 varchar(255)
)

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

Create Table P5 (
    i1 int not null,
    c1 varchar(20) not null,
    c2 varchar(255),
    Constraint PK_P5 Primary Key(i1, c1)
)

Unique Constraints

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

Create Table U1 (
    a int not null, 
    b varchar(20) not null, 
    c varchar(20) not null,
    Constraint U1_pk Primary Key (a),
    Constraint U1_uc Unique (b)
)
go

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.

Create Table F1 (
    a int not null, 
    b varchar(20) not null,
    c varchar(20) not null,
    Constraint F1_pk Primary Key (a, b)
)
go
Create Table F2 (
    i int not null,
    a int not null,
    b varchar(20) not null,
    Constraint F2_pk Primary Key (i),
    Constraint F2_fk1 Foreign Key (a, b) References F1 (a, b)
)
go

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.

Create Table F5 (
    i int not null,
    a int not null,
    b varchar(20) not null,
    Constraint F5_pk Primary Key (i),
    Constraint F5_fk1 Foreign Key (a, b) References F3 (a, b)
        On Update Cascade On Delete Set Null
)
go

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:

Create Table F6 (
    i int not null,
    a char(1) not null,
    b decimal(15,2) not null,
    c decimal(15,2) not null,
    Constraint F6_pk Primary Key (i),
    Constraint F6_chk_a check (a in ('Y','N')),
    Constraint F6_chk_b check (b >= 0 And b <= 1000),
    Constraint F6_chk_c check (c <= (Select Sum(a) From F5))
)
go

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.

Create Table I1 (
    a int not null,
    b varchar(20),
    c varchar(20),
    d varchar(20),
    Constraint I1_pk Primary Key(a)
)
go
Create Index I1_idx1 On Table I1 (b)
go
Create Index I1_idx2 On Table I1 (c ASC, d DESC)
go

Table indexes can be dropped by issuing the Drop Index statement.

Drop Index I1_idx1 On I1

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.

Create Table UI1 (
    a int not null, 
    b varchar(20) not null, 
    c varchar(20) not null,
    Constraint UI1_pk Primary Key (a)
)
go
Create Unique Index UI1_idx_ui On UI1 (c)
go

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.

Create Index T5_idx1 On T5(c2) With Disallow Null

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.

Create Index T5_idx2 On T5(c1) With Ignore Null

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.

Create Table P6 (
    i1 int not null,
    c1 varchar(20) not null,
    c2 varchar(255)
)
go
Create Index P6_idx_pk On P6(i1 Desc) With Primary
go

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:

Drop Index T5_idx2 On T5
go

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

Grant Select on T1 to SalesGroup
go
Grant Select, Insert, Update on T1 to AccountsGroup
go
Revoke Update on T1 from AccountsGroup

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