DevGuru Jet SQL CREATE TABLE Statement


			STATEMENT: CREATE TABLE CREATE TABLE table (field1 type [(size)] [NOT NULL] [index1] [, field2 type [(size)] [NOT NULL] [index2] [, ...]] [, CONSTRAINT multifieldindex [, ...]]) The CREATE TABLE statement is used to create a new table and its fields. At its simplest you can create a table containing only a single field by specifying the name you want to give the table, the field name and the type of data you want the field to contain: CREATE TABLE Names (Name TEXT); You can, of course, include more than one field, and also limit the size of those fields (Text and Binary fields only) by stating the size in parentheses after the data type declaration: CREATE TABLE Names (FirstName TEXT (20), LastName TEXT (20) ); If you require that a particular field must always have valid data entered into it, you can include the expression NOT NULL at the end of the declaration for that field. If you do not enter the required data, you will get a warning message: CREATE TABLE Names (FirstName TEXT (20), LastName TEXT (20) NOT NULL); More often than not, you'll want to place some sort of restriction on the data, or combinations of data, that are entered into fields. You can do this by using the CONSTRAINT clause. The following example expands on the previous ones by adding a Date of Birth field and requiring that the combination of data in all three fields be unique: CREATE TABLE Names (FirstName TEXT (20), LastName TEXT (20), DateOfBirth DATETIME, CONSTRAINT MultiConstraint UNIQUE(FirstName, LastName, DateOfBirth) ); Microsoft warns, "The Microsoft Jet database engine doesn't support the use of any DDL statements with databases produced by any other database engine. Use the DAO (Data Access Objects) Create methods instead."