Article ID: 180841
This article demonstrates some common DDL (Data Definition Language) SQL statements for the Microsoft Jet database engine.
Microsoft Jet provides a full set of Data Definition Language SQL statements, which can be used to create, modify, and delete tables, indexes, and relationships in a Microsoft Access database file. You can use this DDL with the MFC database classes to manipulate an Access database. This article lists some common examples of this category of SQL statements.
You can execute a single DDL statement in this article using the following syntax with the MFC DAO classes:
You can execute the DDL statements in this article using the following syntax with the MFC ODBC classes:
The CREATE TABLE DDL StatementThe following create table statement creates a test table with all of the DDL available to Microsoft Access data types:
Note: You cannot create "AutoNumber Replication," "HyperLink," or "Lookup" type fields using a Microsoft Access DDL SQL statement. These field types are not native Jet field types and can be created and used only by the Microsoft Access user interface. The MyBinary field above is a special fixed-length binary field, which cannot be created via the Microsoft Access user interface but can be created using a SQL DDL statement.
You can create a table with a single-field primary key with a single DDL statement. The following statement creates a table named TestPrimaryKey with a primary key on the MyID field named PK_MyID:
The ALTER TABLE DDL StatementMicrosoft Access DDL supports the ALTER TABLE DDL statement. This statement is useful when you need to remove or add a field to an existing table.
Note: This statement won't let you alter an existing field in an Access table (for example, to change the field's data type).
The following DDL removes the column named MoreInfo from the table TooManyFields:
The following DDL statement adds a column named ExtraInfo to a table named NotEnoughFields:
The ALTER TABLE statement can also be used to create a relationship between two tables.
The following SQL statement creates two tables (Cars and Colors) and then creates a relationship between the Cars table and the Colors table on the ColorID field (each car can have only one color).
Note: There are three separate DDL statements, which must be executed one at a time:
Note: You cannot specify that you want "Cascade Updates" or "Cascade Deletes" with a relationship created using DDL. These features are available only when using the Microsoft DAO (Data Access Objects) interfaces via code or when using the Microsoft Access user interface.
The CREATE INDEX DDL StatementThe CREATE INDEX DDL statement is used to create additional indexes on an existing table.
The following DDL statement adds a single-field, non-unique, ascending index named MyStateIndex to the field State in the table Addresses:
The following DDL statement adds a two-field, unique, ascending index named MyFullNameIndex to the fields FirstName and LastName in the table Addresses:
You can also specify an additional constraint of DISALLOW NULL using the CREATE TABLE DDL statement. Specifying DISALLOW NULL means that the index will prevent the insertion of fields with null values into any of the columns in the index.
The following DDL statement creates a single-field, unique, descending index named MySalaryIndex on the field Salary in the table HRInfo:
This index enforces that every record must have a value for the Salary field.
The DROP DDL StatementThe DROP DDL statement is used to delete existing tables or indexes.
The following DDL statement permanently deletes the table named TempTable:
The following DDL statement permanently deletes the index named MyUnusedIndex on the table OverIndexedTable:
For more information about the Microsoft Jet DDL syntax, refer to the "Microsoft Jet Database Engine Programmer's Guide, Second Edition," Chapter 3, "Data Definition and Integrity."
(http://go.microsoft.com/fwlink/?LinkId=151500)for other considerations.