18 Storing data in tables

 

In chapters 16 and 17, we started creating and manipulating data, and in this chapter, we’ll examine ways to create and manipulate the tables themselves. In many ways, we’ll be getting to the core of SQL language and its use because how the data is stored in tables is at the very heart of any relational database management system (RDBMS). Choosing how data is stored is one of the most important—perhaps the most important decision—that is made in any database.

Don’t worry; this chapter won’t be overly technical. It will still be easy enough for anyone to understand, and because you’ve been querying data with SQL statements that often mirror the English language for a while now, I’m confident that you’ll find the concepts and commands easy to comprehend. This chapter should also reinforce your understanding of things like primary keys and data types.

18.1 Creating a table

As you’ll soon see, creating a table in SQL can be very simple. First, though, you must consider a few things about the table before you write the SQL that creates the table.

18.1.1 Considerations before creating a table

The first step in creating a table is answering three basic questions about the table:

  • What is the name of the table?
  • What are the names of the columns that will be included in the table?
  • What are the data types of those columns?

18.1.2 Creating a table

18.1.3 Adding values to an empty table

18.2 Altering a table

18.2.1 Adding a column to a table

18.2.2 Considerations before adding a column

18.3 Primary keys

18.3.1 Considerations for primary keys

18.3.2 Adding a primary key

18.4 Foreign keys and constraints

18.4.1 Data diagrams

18.4.2 Adding a foreign-key constraint

18.5 Deleting a table, column, or constraint

18.5.1 Deleting a constraint

18.5.2 Deleting a column

18.5.3 Deleting a table

18.6 Lab

18.7 Lab answers