Create Table Examples Using SQL

When creating any objects we use the CREATE keyword. Specifically for tables, we use the CREATE TABLE command. Creating a standard table is fairly straight-forward. There are also important components that can be added to a table during its creation such as a primary key, identity columns, indexes and more. This is a simple example if we were looking to create a basic Company table.

create a company table using ddl example
Create Company Table

At its core, a table can be created at a bare minimum with one column.

CREATE TABLE TableName( ColumnName <datatype>);

We can then continue to include as many columns needed with as many datatypes as needed. Most databases do have column limits. This is something to be aware of although theses limits are normally very high and never reached. Let’s look at a few other create table examples.

Create Table With Primary Key

We can easily include primary keys into a table. A primary key is normally used on a table as a unique identifier. In the example of the Company table, we would want to make the CompanyId field our primary key.

create customer table using sql with a primary key example
Create Company Table w/ Primary Key

As we can see, all we need to include is the primary key keyword (in SQLite db) to apply the primary key value to the table. The database automatically applies a name to the primary key.

company table navigation bar displaying primary key
Create Table With Incremental Column

If we wanted to ensure that our table automatically applied an ID value to the CompanyId field, we could add the AUTOINCREMENT keyword. This will allow the table to auto-populate an id with an increment of 1 starting from an initial value of 1. Every database is slightly different. For example, in SQL Server we would replace the first column with “CompanyId INT IDENTITY(1,1) PRIMARY KEY“. In the case of SQL Server we apply an initial value as well as the incremental increase for each value.

create table incremental values example
Create Table w/ Incremental Values

We can then include an insert statement to see what the output would be.

INSERT INTO Company (CompanyName, CompanyNumber, AddressLine1, AddressLine2, City, State, PostalCode, Country, IsFortune500)
VALUES (‘AllThingsSQL’,’1234′,’543 John Doe Way’,’Apt 7′,’Fantasy Land’,’IL’,’60430′,’United States’,’0′);

As we can see, the resulting output would start at an initial value of 1.

dbeaver company table result output
Create Table With Default Values

Tables can be created with default values. This is helpful if we want to avoid adding logic to an ETL process or script since the table can auto-populate the default instead. As an example, if no country or fortune 500 flag is provided, we can include a default value.

create table setting default values example
Create Table w/ Default Values

We can drop and recreate our company table with the above DDL to then provide an insert statement that DOES NOT include the country and fortune 500 flag.

INSERT INTO Company (CompanyName, CompanyNumber, AddressLine1, AddressLine2, City, State, PostalCode)
VALUES (‘AllThingsSQL’,’1234′,’543 John Doe Way’,’Apt 7′,’Fantasy Land’,’IL’,’60430′);

Normally, we would expect the Country and IsFortune500 fields to return NULL, but since we default the values they return our specified defaults.

dbeaver result output showing all results from company table
CREATE Table With Unique Constraints

Sometimes unique constraints are necessary to apply to a table. This is so that we ensure no duplicate values occur on a specified field. As an example, we may want our CompanyNumber field to be unique on our Company table.

create table with unique column on company table
Create Table w/ Unique Field

With the constraint applied, had we tried to insert the same value twice into CompanyNumber the database would return an error.

SQL Error 19 SQLite Unique Constraint on CompanyNumber
SQLite Unique Constraint Failure
Final Thoughts

These are just some of the create table examples possible. We can also include foreign keys in our tables if needed or even create tables from a select statement. It’s important to recognize the capabilities of table creation and it’s possibilities. We can replace some downstream tasks such as default values and provide uniqueness. These types of tasks help prevent errors in the data and are likely to prevent future headaches. So remember – table creation can do more than just create a table in a database.

Related: DML vs DDL Using SQL – What’s The Difference?