JET Database/Data definition language
- 1 Creating and dropping tables
- 2 Constraints
- 3 Indexing
- 4 Security
Creating and dropping tables
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) )
Tables are dropped by issuing the
Drop Table statement.
DROP TABLE T
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
|See Data integrity for information about data integrity constraints available in JET|
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 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 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
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
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
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 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
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|