Creating and Dropping Tables in SQL

Introduction

Tables are the foundational building blocks of a relational database, organizing data into rows and columns. Learning how to create and delete tables is a critical step in database design and management. In this guide, you’ll learn how to:

• Create tables with SQL.
• Set column types and apply constraints.
• Delete (drop) tables safely and effectively.

Naming a Table

Proper naming conventions improve the readability and maintainability of your database. Follow these guidelines:

• Use snake_case (lowercase with underscores).
• Avoid spaces, dashes, or uppercase letters.
• Name tables in plural form, describing the data they hold.

Examples:

• Good: student_grades, office_locations, people
• Bad: Student Grades, office-locations, person

Writing SQL to Create Tables

Use the following syntax to create a table:

CREATE TABLE table_name (
  column_name data_type,
  column_name data_type,
  ...
  column_name data_type
);
      

Key rules:

• Use parentheses, not curly braces.
• Do not add a trailing comma after the last column definition.

Example: Creating a Table

Consider the following table for storing puppy data:

The SQL to create this table:

CREATE TABLE puppies (
  name VARCHAR(50),
  age_yrs NUMERIC(3,1),
  breed VARCHAR(100),
  weight_lbs INTEGER,
  microchipped BOOLEAN
);
      

Adding a Primary Key

Every table should include a primary key, which uniquely identifies each row. It’s common to use an id column with the INTEGER PRIMARY KEY constraint. For example:

CREATE TABLE puppies (
  id INTEGER PRIMARY KEY,
  name VARCHAR(50),
  age_yrs NUMERIC(3,1),
  breed VARCHAR(100),
  weight_lbs INTEGER,
  microchipped BOOLEAN
);
      

Using NOT NULL Constraints

To ensure that a column cannot be left empty, use the NOT NULL constraint. For example:

CREATE TABLE puppies (
  id INTEGER PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  age_yrs NUMERIC(3,1),
  breed VARCHAR(100),
  weight_lbs INTEGER,
  microchipped BOOLEAN
);
      

This ensures that every puppy entry must have a name.

Dropping a Table

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

DROP TABLE table_name;
      

Example:

DROP TABLE puppies;
      

Warning: Dropping a table is irreversible and will delete all data stored in it. Use this command cautiously, especially in production environments.

What You Learned

• How to create tables with properly named columns and data types.
• How to apply constraints like PRIMARY KEY and NOT NULL.
• How to drop tables safely and understand the risks of doing so.

Mastering table creation and management is an essential skill for designing robust and efficient relational databases. Always plan your schema carefully before executing changes.