18 Creating and Manipulating Tables

You can use structured query language to do more than retrieve and manipulate the data stored in database tables — it can also be used to perform database and table maintenance tasks, including creating, editing, and deleting database tables. Most DBMSs have interactive tools for completing these tasks, but it is helpful to have some basic understanding of the SQL statements that perform them, because the syntax highlights some important database concepts. This lesson describes how to create, edit, and delete database tables.

18.1 Creating Tables

As with all of the SQL statements covered in this resource, creating database tables involves using SQL keywords. The statement for creating a database table starts with the keywords CREATE and TABLE. The keyword CREATE is used whenever you want to add a new object to the database. A table is a database object, which is why the keyword TABLE follows CREATE — together they tell the DBMS that a new table object is being added to the database.

When you add a table to the database, you provide the DBMS with the details of the table. These details include:

1. The name of the table.
2. The list of columns that will be stored on the table, including the details of each column.
3. The columns that will act as the primary and foreign keys of the table.

The generic syntax for adding a table to the database is:

CREATE TABLE tablename (
    column1_name column1_details,
    column2_name column2_details,
    optional_table_details
);

The table name is specified after the keywords CREATE TABLE. The individual columns of the table are detailed as a comma-separated list in parentheses after the table name. Primary and foreign keys are defined either as part of a column's details or as optional table-level details listed after the columns.

18.1.1 Table Names

Table names must be unique within a database — two tables in the same database can't have the same name. Additionally, a table name cannot be a reserved word; you can't name a table select. While these are the only hard rules for naming database tables, there are a few suggestions worth keeping in mind:

1. Table names should be descriptive. A table name should convey what is stored in the table clearly enough that users don't have to guess. If the table names in a database are too ambiguous, the database becomes difficult to use.
2. Table names should be brief. Writing SQL statements can already feel tedious; overly verbose table names only make it worse.
3. Table names may include most characters typically allowed in a filename (though not /, \, or .), but it's best to avoid spaces in table names.
4. Use a consistent naming convention for all table names in a database. Table names should all look alike — for example, if one table name is singular (customer rather than customers), all of them should be. Naming conventions make a database easier to use, because its elements become predictable. If some tables are named in the singular and others in the plural, users have to keep checking the schema diagram to know which form to use.

The next suggestion is somewhat debated among database experts, but it's practical advice for avoiding a common problem:

5. Use snake_case for table names. Snake case replaces all the spaces in a name with underscore characters — for example, a table that stores museum hours would be named museum_hours. Snake case is generally easier to read than other naming conventions, such as CamelCase (capitalizing the first letter of each word in a name with no spaces), and it reduces the potential for ambiguity in table names.

An example of the syntax used to add a table named vendor to the database is:

CREATE TABLE vendor ( ... );

18.1.2 Defining Table Columns

Columns are defined as a comma-separated list. Each column definition specifies a column name, the type of data that will be stored in the column, and — optionally — any constraints on how data can be stored in that column. A name and data type are required for every column; additional constraints are optional.

18.1.2.1 Column Names

Column names must be unique within a table. The same naming suggestions that apply to table names apply to column names. Additionally, it's a good idea to begin each primary key column name with the name of its table — for example, the primary key of the museum table should be named museum_id rather than just id.

18.1.2.2 Data Types

You must also define the type of data that will be stored in each column. PostgreSQL supports a wide range of data types, but the ones you'll use most often store text, integer numbers, decimal numbers, dates and times, and boolean (true/false) values.

An example of the syntax used to define the name column on the vendor table with a data type of TEXT is:

CREATE TABLE vendor (
    name TEXT
);

18.1.2.3 Column Constraints

You can also add constraints on the values that can be stored in a column. These constraints are enforced by the DBMS — when a user tries to add a row that violates one of them, the DBMS rejects the row rather than saving it to the table.

The first constraint you can add to a column is the unique constraint, applied with the keyword UNIQUE. It ensures that every value stored in that column is different from every other value already in the column. If a user tries to add a row with a value that duplicates an existing one, the DBMS will reject the new row.

You can also add a constraint that ensures a column has a value whenever a new row is added to the table. This is done using the keywords NOT NULL, which essentially makes a value required in that column for every row.

A column can also be designated as the primary key using a column constraint, with the keywords PRIMARY KEY. Recall that the values in a primary key column must be both unique and not null — PRIMARY KEY combines the UNIQUE and NOT NULL constraints on a single column. PostgreSQL will also add an index on the column automatically to keep primary-key lookups fast.

An example of the syntax used to define the vendor table, with vendor_id as an integer primary key and the name column as a required (NOT NULL) piece of text, is:

CREATE TABLE vendor (
    vendor_id INTEGER PRIMARY KEY,
    name      TEXT NOT NULL
);

You can also add custom constraints to the columns of a table using the keyword CHECK. A CHECK constraint defines a logical test that the values for a column must satisfy before a new row can be added to the table. For example, you could add a check constraint to ensure that a new employee's salary falls within an appropriate range.

18.1.3 Defining Table-Level Options

After the columns have been defined in a CREATE TABLE statement, table-level constraints can be added. The two most common table-level constraints are primary and foreign keys. Generally, a primary key is designated as part of a single column's definition. If multiple columns are used together as a composite primary key, though, the primary key must instead be added as a table-level constraint, using the keywords PRIMARY KEY followed by a comma-separated list of the columns that make up the composite key, in parentheses. An example of the syntax used to define a composite primary key made up of the product_id, sale_id, and product_size columns is:

PRIMARY KEY (product_id, sale_id, product_size)

Foreign keys are also added as table-level constraints, and a foreign key constraint has three parts. First, the column to be used as the foreign key is identified — this column must already exist on the table. Second, the table (and, implicitly, its primary key) that the foreign key references is specified. Third, the constraint designates what should happen if the referenced row on the related table is deleted. The following syntax demonstrates how museum_id would be defined as a foreign key on the vendor table, referencing the museum table:

CREATE TABLE vendor (
    vendor_id INTEGER PRIMARY KEY,
    ...
    museum_id INTEGER,
    FOREIGN KEY (museum_id) REFERENCES museum (museum_id) ON DELETE SET NULL
);

Notice the set of keywords in the foreign key constraint. FOREIGN KEY designates the column that will act as the foreign key. REFERENCES specifies the table — and, in parentheses, the column on that table — that the foreign key relates to. ON DELETE SET NULL ensures that if a museum is deleted from the museum table, the corresponding museum_id values on the vendor table are set to NULL, so the vendor table never references a museum that no longer exists.

Putting all of these pieces together produces a complete table creation statement. A statement to create the vendor table in the museum database would be:

CREATE TABLE vendor (
    vendor_id INTEGER PRIMARY KEY,
    name      TEXT NOT NULL,
    country   TEXT NOT NULL,
    museum_id INTEGER,
    FOREIGN KEY (museum_id) REFERENCES museum (museum_id) ON DELETE SET NULL
);

If the statement executes without errors, PostgreSQL responds that the table was created, and the (still empty) table is added to the database. The SQL statements used to add data to a table are discussed in the lesson on inserting data. If an error occurs while the statement is executing, PostgreSQL responds with an error message describing the problem instead.

18.2 Deleting Tables

A common error occurs when you try to add a table that already exists in the database — if you want to replace an existing table, you must remove it first. To remove a table from the database, you use the keywords DROP TABLE. DROP is the keyword used to remove objects from the database, and TABLE designates that the object being removed is a table.

When a table is dropped from the database, it is completely removed, along with all of the data stored on it. The syntax used to remove the vendor table you just created is:

DROP TABLE vendor;

18.3 Editing Tables

From time to time, you will need to make changes to an existing database table. The keywords used to change a table are ALTER TABLE; ALTER is the keyword used to change a database object. The syntax for changing a database table is:

ALTER TABLE tablename
statements_that_define_the_changes;

For example, you can add a column to, or remove a column from, an existing table. The syntax used to add a column named vendor_phone to the existing vendor table is:

ALTER TABLE vendor
ADD COLUMN vendor_phone TEXT;

Try the SQL statement.

CREATE TABLE vendor (
    vendor_id INTEGER PRIMARY KEY,
    name      TEXT NOT NULL,
    country   TEXT NOT NULL,
    museum_id INTEGER,
    FOREIGN KEY (museum_id) REFERENCES museum (museum_id) ON DELETE SET NULL
);

ALTER TABLE vendor
ADD COLUMN vendor_phone TEXT;

Note that if you ran the DROP TABLE statement in the last section, your copy of the vendor table was deleted from the database, which is why this example re-creates the vendor table first. If you didn't drop the table, you'll get an error when this example tries to create it again.

Similarly, the syntax used to alter the table and remove the vendor_phone column is:

ALTER TABLE vendor
DROP COLUMN vendor_phone;

Tables can also be altered to add primary and foreign keys after the table has already been created, using ADD CONSTRAINT. For example, the following statement adds a foreign key to the vendor table's museum_id column after the fact:

ALTER TABLE vendor
ADD CONSTRAINT vendor_museum_fk
FOREIGN KEY (museum_id) REFERENCES museum (museum_id) ON DELETE SET NULL;

Finally, ALTER TABLE can also be used to rename a table, with the RENAME TO clause:

ALTER TABLE vendor
RENAME TO supplier;

18.4 Summary

This lesson described how to write SQL statements to manipulate database tables. Specifically, it described how to add database tables using the CREATE TABLE keywords, including how to define table columns and table-level constraints. It also described how to remove tables from a database and how to edit existing tables.