Learning MySQL and MariaDB

Chapter 5. Altering Tables

Despite the best planning, you will need occasionally to change the structure or other aspects of your tables. We cannot imagine everything that we might want to do with a table, or how the data might look when it’s entered. Altering a table, though, is not very difficult. Because of these factors, you shouldn’t worry too much about getting the table structure exactly right when creating a table. You should see tables as more fluid. Perhaps the term table structure makes that difficult to accept: the words table and structure have such rigid senses to them. To offset these images, perhaps a modified version of a cliché would be useful to give you a truer sense of the reality of table structures: they’re not made of stone or wood, but of digital confines that are easily altered. I suspect that sentence won’t be quoted much, but it’s a useful perspective.

In this chapter, we will explore the ways to alter tables: how to add and delete columns, how to change their data types, how to add indexes, and how to change table and column options. This chapter will also include some precautions about potential data problems you can cause when altering a table containing data.

Prudence When Altering Tables

Before doing any structural changes to a table, especially if it contains data, you should make a backup of the table to be changed. You should do this even if you’re making simple changes. You might lose part of the data if you inadvertently change the column to a ...