CREATE TABLE Syntax
CREATE TABLE Tablename ( Columnname Datatype Field Size, [NULL | NOT NULL] [optional constraints] );
To create a table, you must define a table name, column names, data types, and field sizes. In the preceding syntax, the CREATE TABLE keywords are used to instruct the database to create a new table and must be followed by the name of the table. The CREATE TABLE syntax also requires opening and closing parentheses. The open parenthesis follows the name of the table and the close parenthesis is located at the end of the CREATE TABLE script. The closing semicolon tells Microsoft Access where the query ends. The closing semicolon is optional in Access, although getting into the habit of using it will be helpful when you start building complex SQL statements consisting of multiple declarations. The following SQL script creates a table named Toys:
CREATE TABLE Toys ( );
While this is a good example of a table, there is a critical element missing. A table is not useful unless it has fields to hold data, so let's add a few fields to the SQL script.
CREATE TABLE Toys ( ToyID INTEGER, ToyName CHAR (30), Price MONEY, Description CHAR (40) );
Notice that in the preceding script, the SQL keywords are typed in all caps. While SQL script is not case sensitive, it is accepted practice to capitalize keywords. Keywords in all caps stand out better and make your SQL script more readable. We highly recommend the capitalization of keywords.
Another widely implemented practice that is not required in SQL programming is to format the code. SQL commands execute without errors if placed on the same line, but again your SQL script is much easier to read and, more important, to debug when you break it up into several lines.
Note: When you create table and column names that contain spaces, enclose the names in brackets ([ ]). For example, the following script creates a table named Furniture with column names that contain spaces:
CREATE TABLE Furniture ( [Furniture ID] INTEGER, [Furniture Name] CHAR (30), [Furniture Price] MONEY );
In this tutorial:
- Creating Tables and Inserting Records
- Data Definition Language Component
- CREATE TABLE Syntax
- SQL Data Types
- NULL/NOT NULL Constraint
- PRIMARY KEY Constraint
- FOREIGN KEY Constraint
- Adding Constraints to Existing Tables
- Constraint Syntax
- Inserting Records
- Inserting Data without Specifying Column Names