Chapter 4: Creating and Modifying Database Objects

Creating tables

Creating tables is one of the fundamental tasks in SQL. A table is a collection of related data organized into rows and columns. You create one with the CREATE TABLE statement:

CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  username VARCHAR(50),
  email VARCHAR(100)
);

This creates a table called users with three columns: id, username, and email. The id column is the primary key, which means it uniquely identifies each row in the table. The username and email columns are both VARCHAR columns and can store up to 50 and 100 characters respectively.

Altering tables

Sometimes you need to change an existing table, either to add new columns or to change the structure of an existing column. You do this with the ALTER TABLE statement.

Adding a column

ALTER TABLE users ADD COLUMN full_name VARCHAR(100);

This adds a new column called full_name to the users table. SQL Server and Oracle leave out the word COLUMN: ALTER TABLE users ADD full_name VARCHAR(100);.

Modifying a column

ALTER TABLE users MODIFY COLUMN email VARCHAR(255);

This increases the maximum length of the email column from 100 to 255 characters.

That’s MySQL’s syntax. Changing a column is one of the places where databases differ most:

-- PostgreSQL
ALTER TABLE users ALTER COLUMN email TYPE VARCHAR(255);

-- SQL Server
ALTER TABLE users ALTER COLUMN email VARCHAR(255);

-- Oracle
ALTER TABLE users MODIFY email VARCHAR2(255);

SQLite can’t change a column at all. You create a new table with the new definition, copy the rows across, and drop the old table.

Renaming a column

ALTER TABLE users RENAME COLUMN full_name TO display_name;

This renames the full_name column you added above to display_name. MySQL 8.0 and later, PostgreSQL, Oracle, and SQLite all accept RENAME COLUMN. SQL Server uses a stored procedure instead: EXEC sp_rename 'users.full_name', 'display_name', 'COLUMN';.

Dropping tables

If you no longer need a table, the DROP TABLE statement deletes it from the database:

DROP TABLE users;

This drops the users table from the database. Be careful with this statement, because it permanently deletes all the data in the table. Make sure you have a backup of your data before you use DROP TABLE.

If you’re following along, run the CREATE TABLE statement from the start of this chapter again before you go on. Chapter 5 adds rows to the users table.

Keyboard shortcuts