Microsoft Access SQL Keywords: TABLE

	SQL Keywords: TABLE

Description

The TABLE keyword is used to identify a table in an action. The keyword can be used when creating a table. The formula to use would be:

CREATE TABLE Employees(ColumnName DataType)

To get more information, consult the techniques of creating a table in SQL.

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 PK_People 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)
);

Modifying a Table

The TABLE keyword can be used when modifying the structure of a table, such as when adding a new column to an existing table. The formula to use would be:

ALTER TABLE TableName
ADD COLUMN ColumnName DataType

Here is an example:

ALTER TABLE Employees ADD COLUMN EmailAddress Text(100);