Chapter 3: Defining Tables

 

In the previous chapter, we learned to create and use a database. In this chapter, we’ll learn to add tables to our database. We’ll also learn to alter and delete the tables if necessary. This is a relatively long chapter, so take your time to slowly go through it.

 

Creating Tables

 

First, let’s look at how we can create tables to add to our database. To create a table, we use the following syntax:

 

CREATE TABLE table_name (
      column_name1 datatype [column constraints],
      column_name2 datatype [column constraints],
      …
      [table constraints],
      [table constraints]
);      

 

Let’s discuss the syntax in detail.

 

Tables in SQL databases are organized in rows and columns. Suppose we want to create a table to store information about the employees of a company. We can design the table as shown below:

 

 

Each column in the table stores a specific piece of information about the employee (such as the id, name and gender of the employee).

 

Each row, on the other hand, stores information about...

Altering Tables

 

We’ve covered quite a bit in this chapter so far. To recap, we learned that to create a table, we need to do two things:

 

1) Specify the columns by stating their names, data types and constraints (if any)
2) Specify any table constraints that the table must fulfil

 

Now that we have created the two tables that we need, let’s move on to learn how we can modify tables. This is useful if we need to make any changes to our tables after creating them.

 

Table Names

 

The first thing we can modify is the table name. To do that, we use the syntax

 

RENAME TABLE old_name TO new_name;

 

Let’s change the name of our co_employees table to employees now. To do that, execute the following statement:

 

RENAME TABLE co_employees TO employees;

 

Columns and Table Constraints

 

Next, let’s learn to alter some of the columns and table constraints of our tables. For each of the alterations...

Deleting Tables

 

Last but not least, before we end this chapter, let’s learn to delete a table. To do that, we use the syntax below:

 

DROP TABLE [IF EXISTS] table_name;

 

For instance, if we want to delete a table called demo, we write

 

DROP TABLE IF EXISTS demo;

 

With this, we’ve come to the end of Chapter 3.