SQL CREATE TABLE for Beginners

This article contains basic SQL CREATE TABLE statements that beginners can use to create basic tables for their database projects.

Basic Syntax

The basic syntax for creating tables in SQL goes like this:

CREATE TABLE TableName (
    Column1 datatype optional_constraints,
    Column2 datatype optional_constraints,
    Column3 datatype optional_constraints,
    ...
);

Where:

• TableName is the name you want to give to the table
• Column1, etc are the names you give to your columns
• datatype is the datatype that you assign to each column. Most RDBMSs require that you assign a datatype to each column. In SQLite, it’s optional.
• optional_constraints is an optional list of constraints that you apply against a column.

Note that most DBMSs use a more complex syntax than this (i.e. they offer more options), but this is generally the basic syntax required to create a table. Most beginners start with this syntax, then learn more advanced options as they progress.

Basic Example

Here’s a basic example:

CREATE TABLE Pets
(
    PetId       int NOT NULL PRIMARY KEY,
    PetTypeId   int NOT NULL,
    OwnerId     int NOT NULL,
    PetName     varchar(60) NOT NULL,
    DOB         date NULL
);

Here are some points to note:

• Table and column names can be in any case. For example, instead of PetId I could have called it petid, PETID, or pet_id. Some DBMSs (e.g. Postgres) are case-sensitive when you query tables and their column names, while others are not (e.g. SQL Server).
• Some DBMSs use different names for their data types.
• NOT NULL is a table constraint that specifies that this column must contain a value (i.e. it cannot contain NULL values).
• NULL means that the column is allowed to contain NULL values. Some DBMSs (such as DB2) don’t support the NULL keyword, and so you will need to omit it when working with such DBMSs.
• PRIMARY KEY adds a primary key constraint to the table. It’s good practice to specify a primary key on all your tables.
• If a table of the same name already exists in the database, you’ll get an error. You’ll need to drop the existing table or alter it (or change the name of the one you’re creating).

Create a Table with Foreign Keys

The following example creates a table with two foreign keys. Foreign keys are used to create a relationship between two tables.

CREATE TABLE Pets
(
    PetId       int NOT NULL PRIMARY KEY,
    PetTypeId   int NOT NULL REFERENCES PetTypes (PetTypeId),
    OwnerId     int NOT NULL REFERENCES Owners (OwnerId),
    PetName     varchar(60) NOT NULL,
    DOB         date NULL
);

The REFERENCES clause is used to define a foreign key against two columns. In our case, the foreign keys enforce the following:

• The Pets.PetTypeId column references the PetTypes.PetTypeId column (any value that goes into the Pets.PetTypeId column must have a matching value in the PetTypes.PetTypeId column).
• The Pets.OwnerId column references the Owners.OwnerId column (any value that goes into the Pets.OwnerId column must have a matching value in the Owners.OwnerId column).

Foreign keys can also be added later, using the ALTER TABLE statement if required.

Create a Table with DEFAULT Constraint

The following example creates a table with a DEFAULT constraint.

CREATE TABLE OrderItems
(
    OrderId     int NOT NULL PRIMARY KEY,
    OrderItem   int NOT NULL,
    ProductId   int NOT NULL,
    Quantity    int NOT NULL DEFAULT 1,
    ItemPrice   decimal(8,2) NOT NULL 
);

Create a Table with a Time Stamp Column

You can use DEFAULT constraints to automatically insert the current date and time into a column whenever a new row is inserted. This is often referred to as inserting a time stamp.

Different DBMSs use different functions to return the current date.

Here’s an example of doing it in SQL Server.

CREATE TABLE Users
(
    UserId         int NOT NULL PRIMARY KEY,
    FirstName      varchar(60) NOT NULL,
    LastName       varchar(60) NOT NULL,
    DateInserted   datetime DEFAULT CURRENT_TIMESTAMP
);

In SQL Server, CURRENT_TIMESTAMP is the ANSI equivalent of the GETDATE() function.

You can alternatively use SYSDATETIME() for a higher precision time stamp:

CREATE TABLE Users
(
    UserId         int NOT NULL PRIMARY KEY,
    FirstName      varchar(60) NOT NULL,
    LastName       varchar(60) NOT NULL,
    DateInserted   datetime2 DEFAULT SYSDATETIME()
);

In order to support the higher precision, the column’s data type must be datetime2.

SQL Server has quite a few data types for date/time values. See SQL Server date and time functions for a list.

Each DBMS has its own functions for returning the date and time. Here’s a list of the main ones:

DB2	CURRENT_DATE
MySQL	CURRENT_DATE or NOW()
Oracle	SYSDATE
PostgreSQL	CURRENT_DATE
SQL Server	CURRENT_TIMESTAMP, GETDATE() or SYSDATETIME()
SQLite	datetime('now')

Also see:

• SQL Server Date and Time Functions
• SQLite Date and Time Functions

Create a Column with an Automatically Incrementing Value

Sometimes you need to create a column that automatically increments a value whenever a new row is inserted. These are typically referred to as “auto-increment” or “identity” columns, depending on the DBMS being used.

In SQL Server, use the IDENTITY argument:

CREATE TABLE Users
(
    UserId         int NOT NULL IDENTITY(1,1) PRIMARY KEY,
    FirstName      varchar(60) NOT NULL,
    LastName       varchar(60) NOT NULL,
    DateInserted   datetime2 DEFAULT SYSDATETIME()
);

In this case, we use IDENTITY(1,1).

• The first 1 is the seed. This specifies what value to start the count (i.e. this is the value of the very first row inserted into the table).
• The second 1 is the increment. This is the incremental value added to the identity value of the previous row loaded.

Each DBMS has its own keyword for creating an auto-incrementing column.

• In MySQL and MariaDb, use AUTO_INCREMENT
• In SQLite, you have a couple of options (including the AUTOINCREMENT keyword)
• In PostgreSQL, use SERIAL
• From Oracle Database 12c, you can now use the IDENTITY keyword for creating auto-incrementing columns. An example might look like: UserId NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY

Advanced Options

Most DBMSs allow you to specify advanced options when creating tables. The options available will depend largely on your DBMS.

Refer to your DBMS documentation if you need options that aren’t specified in this article.

Many advanced options can be set via the GUI (depending on the tool you use to connect to your database). You can always create or modify a table via the GUI, then use its “Script as Create” (or similar) option to see the SQL code that you’d need to run in order to create such a table. You can take this code and change the table names, column names, etc to create a whole new table based on the definition of the previous one.