How To Create and Manage Tables in SQL

Introduction

Tables are the primary organizational structure in SQL databases. They comprise a number of columns that reflect individual attributes of each row, or record, in the table. Being such a fundamental aspect of data organization, it’s important for anyone who works with relational databases to understand how to create, change, and delete tables as needed.

In this guide, we’ll go over how to create tables in SQL, as well as how to modify and delete existing tables.

Prerequisites

In order to follow this guide, you will need a computer running some type of relational database management system (RDBMS) that uses SQL. The instructions and examples in this guide were validated using the following environment:

• A server running Ubuntu 20.04, with a non-root user with administrative privileges and a firewall configured with UFW, as described in our initial server setup guide for Ubuntu 20.04.
• MySQL installed and secured on the server, as outlined in How To Install MySQL on Ubuntu 20.04. This guide was verified with a non-root MySQL user, created using the process described in Step 3.

You’ll also need a database and table loaded with some sample data with which you can practice using wildcards. If you don’t have these, you can read the following Connecting to MySQL and Setting up a Sample Database section for details on how to create a database and table which this guide will use in examples throughout.

Connecting To MySQL and Setting Up a Sample Database

If your SQL database system runs on a remote server, SSH into your server from your local machine:

ssh sammy@your_server_ip

Then open up the MySQL server prompt, replacing sammy with the name of your MySQL user account:

mysql -u sammy -p

Create a database named tablesDB:

CREATE DATABASE tablesDB;

If the database was created successfully, you’ll receive output like this:

Output
Query OK, 1 row affected (0.01 sec)

To select the tablesDB database, run the following USE statement:

USE tablesDB;

Output
Database changed

With that, you’re ready to follow the rest of the guide and begin learning about how to create and manage tables in SQL.

Creating Tables

To create a table in SQL, use the CREATE TABLE command, followed by your desired name for the table:

CREATE TABLE table_name;

Be aware that, as with every SQL statement, CREATE TABLE statements must end with a semicolon (;).

This example syntax will create an empty table that doesn’t have any columns. To create a table with columns, follow the table name with a list of column names and their corresponding data types and constraints, bracketed by parentheses and separated by commas:

CREATE TABLE table_name (
column1_name column1_data_type,
column2_name column2_data_type,
. . .
columnN_name columnN_data_type
);

As an example, say you wanted to create a table to record some information about your favorite parks in New York City. After deciding what attributes you’d like to record about each park, you would then decide on column names for each of those attributes as well as the appropriate data type for each one:

• parkName: The name of each park. There is a wide variance in the length of park names, so the varchar data type with a maximum length of 30 characters would be appropriate.
• yearBuilt: The year the park was built. Although MySQL has the year data type, this only allows values from 1901 to 2155. New York City has several parks built before 1901, so you might instead use the int data type.
• firstVisit: The date of your first visit to each park. MySQL has the date data type which you might use for this column. It stores data in the format of YYYY-MM-DD.
• lastVisit: The date of your most recent visit to each park. Again, you could use the date type for this.

To create a table named faveParks with columns that have these names and data types, you would run the following command:

CREATE TABLE faveParks (
parkName varchar(30),
yearBuilt int,
firstVisit date,
lastVisit date
);

Output
Query OK, 0 rows affected (0.01 sec)

Keep in mind that this only creates the table’s structure, as you haven’t added any data to the table.

You can also create new tables out of existing ones with the CREATE TABLE AS syntax:

CREATE TABLE new_table_name AS (
SELECT column1, column2, . . . columnN
FROM old_table_name
);

Instead of following the new table’s name with a list of columns and their data types, you follow it with AS and then, in parentheses, a SELECT statement that returns whatever columns and data from the original table you’d like to copy over to the new table.

Note that if the original table’s columns hold any data, all that data will be copied into the new table as well. Also, for clarity, this example syntax includes a SELECT query that only has the requisite FROM clause. However, any valid SELECT statement will work in this place.

To illustrate, the following command creates a table named parkInfo from two columns in the faveParks table created previously:

CREATE TABLE parkInfo AS (
SELECT parkName, yearBuilt
FROM faveParks
);

Output
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

If the faveParks table had held any data, the data from its parkName and yearBuilt columns would have been copied to the parkInfo table as well, but in this case both tables will be empty.

If you try creating a table using the name of an existing table, it will cause an error:

CREATE TABLE parkInfo (
name varchar(30),
squareFootage int,
designer varchar(30)
);

Output
ERROR 1050 (42S01): Table 'parkInfo' already exists

To avoid this error, you can include the IF NOT EXISTS option in your CREATE TABLE command. This will tell the database to check whether a database with the specified name already exists and, if so, to issue a warning instead of an error:

CREATE TABLE IF NOT EXISTS parkInfo (
name varchar(30),
squareFootage int,
designer varchar(30)
);

Output
Query OK, 0 rows affected, 1 warning (0.00 sec)

This command will still fail to create a new table, since the table named parkInfo still exists. Notice, though, that this output indicates that the CREATE TABLE statement led to a warning. To view the warning message, run the diagnostic SHOW WARNINGS statement:

SHOW WARNINGS;

Output
| Level | Code | Message                         |
+-------+------+---------------------------------+
| Note  | 1050 | Table 'parkInfo' already exists |
+-------+------+---------------------------------+
1 row in set (0.00 sec)

As this output indicates, the same error you received previously has been registered as a warning because you included the IF NOT EXISTS option. This can be useful in certain cases, like when running transactions; an error will cause the entire transaction to fail, while a warning will mean only the statement that caused it will fail.

Altering Tables

There are times when you may need to change a table’s definition. This is different from updating the data within the table; instead, it involves changing the structure of the table itself. To do this, you would use the ALTER TABLE syntax:

ALTER TABLE table_name ALTER_OPTION sub_options . . . ;

After beginning the ALTER TABLE statement, you specify the name of the table you want to change. Then, you pass whichever options are available in your RDBMS to perform the alteration you have in mind.

For example, you may want to rename the table, add a new column, drop an old one, or change a column’s definition. You can continue reading to practice these examples on the faveParks table created previously in the Creating Tables section.

To change the name of the faveParks table, you could use the RENAME TO syntax. This example changes the faveParks table’s name to faveNYCParks:

ALTER TABLE faveParks RENAME TO faveNYCParks;

Output
Query OK, 0 rows affected (0.01 sec)

To add a new column, you’d pass the ADD COLUMN option. The following example adds a column named borough, which holds data of the varchar type, but with a maximum length of 20 characters, to the faveNYCParks table:

ALTER TABLE faveNYCParks ADD COLUMN borough varchar(20);

Output
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

To delete a column and any data it holds from a table, you could use the DROP TABLE syntax. This example command drops the borough column:

ALTER TABLE faveNYCParks DROP COLUMN borough;

Many SQL implementations allow you to change a column’s definition with ALTER TABLE. The following example uses MySQL’s MODIFY COLUMN clause, changing the yearBuilt column to use the smallint data type rather than the original int type:

ALTER TABLE faveNYCParks MODIFY COLUMN yearBuilt smallint;

Be aware that every RDBMS has different options for what you can change with an ALTER TABLE statement. To understand the full scope of what you can do with ALTER TABLE, you should consult your RDBMS’s official documentation to learn what ALTER TABLE options are available for it.

Here’s the official documentation on the subject for a few popular open-source databases:

• MySQL ALTER TABLE Documentation
• PostgreSQL ALTER TABLE Documentation
• SQLite ALTER TABLE Documentation

Deleting Tables

To delete a table and all of its data, use the DROP TABLE syntax:

DROP TABLE table_name;

You can delete multiple tables with a single DROP statement by separating their names with a comma and a space, like this:

DROP TABLE table1, table2, table3;

To illustrate, the following command will delete the faveNYCParks and parkInfo tables created earlier in this guide:

DROP TABLE IF EXISTS faveNYCParks, parkInfo;

Note that this example includes the IF EXISTS option. This has the opposite function of the IF NOT EXISTS option available for CREATE TABLE. In this context, IF EXISTS will cause the DROP TABLE statement to return a warning instead of an error message if one of the specified tables doesn’t exist.

Conclusion

By reading this guide, you learned how to create, change, and delete tables in SQL-based databases. The commands outlined here should work on any database management system that uses SQL. Keep in mind that every SQL database uses its own unique implementation of the language, so you should consult your DBMS’s official documentation for a more complete description of each command and their full sets of options.

If you’d like to learn more about working with SQL, we encourage you to check out the other tutorials in this series on How To Use SQL.