Microsoft Access SQL Keywords: CREATE

	SQL Keywords: CREATE

Description

The CREATE keyword is mostly used in conjunction with the TABLE keyword to create a table. When creating the table, you must also create at least one column. This is done using the following formula:

CREATE TABLE Name(ColumnName DataType Options)

Examples

Here is an example that creates a simple table with one column:

CREATE TABLE Customers(FullName Text);

Here is an example that creates a simple table with two columns:

CREATE TABLE Customers(FirstName Text, LastName Char);

Here is an example that creates a table with many columns:

CREATE Table Employees(FirstName Text,
                       LastName Text,
                       EmailAddress Varchar,
                       HomePhone Char);

Here is an example of a table with two fields with one field created as a primary key:

CREATE TABLE Genders(GenderID COUNTER(1,1) PRIMARY KEY NOT NULL,
                     Gender varchar(20));

Here is another example of creating a primary key, this time, using the CONSTRAINT keyword:

CREATE TABLE Persons(PersonID COUNTER(1,1) NOT NULL,
                     FirstName varchar(20),
                     LastName varchar(20) NOT NULL,
                     CONSTRAINT PrimKeyPeople PRIMARY KEY(PersonID));

Here is an example of a table that will have a primary key named ReceiptNumber:

CREATE TABLE RentalOrders(ReceiptNumber COUNTER(1001, 1) NOT NULL PRIMARY KEY,
                          OrderPreparedBy TEXT(10), " & _
                 	  OrderFinalizedBy TEXT(10));

Here is an example with various columns that use different data types:

CREATE TABLE Transactions(TransactionID COUNTER(1001, 1) NOT NULL PRIMARY KEY,
                 	  TransactionDate DATE,
                 	  EmployeeID LONG,
                 	  CustomerID LONG,
                 	  TransactionTypeID LONG,
                 	  DepositAmount DOUBLE,
                 	  DepositTypeID LONG,
                 	  WithdrawalAmount DOUBLE,
                 	  WithdrawalTypeID LONG,
                 	  ServiceCharge DOUBLE,
                 	  ChargeReasonID LONG,
                 	  Notes MEMO);

Here is an example that creates a foreign key:

CREATE TABLE Persons
(
    PersonID AUTOINCREMENT(1,1) NOT NULL,
    FirstName varchar(20),
    LastName varchar(20) NOT NULL,
    GenderID Integer REFERENCES Genders(GenderID),
    CONSTRAINT PK_Persons PRIMARY KEY(PersonID)
);