• Choose a category
• All
• Classic-Fiction

• Make sure every table has a primary key (refer to Chapter 15, “Joining Tables,” if you have forgotten what this is) and use it as the WHERE clause whenever possible. (You may specify individual primary keys, multiple values, or value ranges.)

• Before you use a WHERE clause with an UPDATE or a DELETE, first test it with a SELECT to make sure it is filtering the right records—it is far too easy to write incorrect WHERE clauses.

• Use database enforced referential integrity (refer to Chapter 15 for this one, too) so MariaDB does not allow the deletion of rows that have data in other tables related to them.

* * *

Caution: Use with Caution

The bottom line is that MariaDB has no Undo button. Be very careful using UPDATE and DELETE, or you might find yourself updating and deleting the wrong data.

* * *

Summary

In this chapter, you learned how to use the UPDATE and DELETE statements to manipulate the data in your tables. You learned the syntax for each of these statements, as well as the inherent dangers they expose. You also learned why WHERE clauses are so important in UPDATE and DELETE statements, and you were given guidelines to follow to help ensure that data does not get damaged inadvertently.

21. Creating and Manipulating Tables

In this chapter you learn the basics of table creation, alteration, and deletion.

Creating Tables

MariaDB SQL statements are not used just for table data manipulation. Indeed, SQL statements can be used to perform all database and table operations, including the creation and manipulation of tables themselves.

There are generally two ways to create database tables:

• Using an administration tool (like the ones discussed in Chapter 2, “Introducing MariaDB”) that can be used to create and manage database tables interactively.

• Tables may also be manipulated directly with MariaDB SQL statements.

To create tables programmatically, the CREATE TABLE SQL statement is used. It is worth noting that when you use interactive tools, you are actually using MariaDB SQL statements. Instead of your writing these statements, however, the interface generates and executes the SQL seamlessly for you (the same is true for changes to existing tables).

* * *

Tip: Additional Examples

For additional examples of table creation scripts, see the code used to create the sample tables used in this book.

* * *

* * *

Note: Just the Basics

MariaDB supports a vast array of table creation options, far more than a single chapter can do justice to. In this chapter we cover the basics, just so you can get a feel for what’s involved in table creation, and so that the accompanying table creation scripts make sense. To learn more about all that CREATE TABLE can do, consult the MariaDB documentation.

* * *

Basic Table Creation

To create a table using CREATE TABLE, you must specify the following information:

• The name of the new table specified after the keywords CREATE TABLE.

• The name and definition of the table columns separated by commas.

The CREATE TABLE statement may also include other keywords and options, but at a minimum you need the table name and column details. The following MariaDB SQL statement creates the customers table used throughout this book:

Input

CREATE TABLE customers

(

cust_id int NOT NULL AUTO_INCREMENT,

cust_name char(50) NOT NULL ,

cust_address char(50) NULL ,

cust_city char(50) NULL ,

cust_state char(5) NULL ,

cust_zip char(10) NULL ,

cust_country char(50) NULL ,

cust_contact char(50) NULL ,

cust_email char(255) NULL ,

PRIMARY KEY (cust_id)

) ENGINE=Aria;

Analysis

As you can see in the preceding statement, the table name is specified immediately following the CREATE TABLE keywords. The actual table definition (all the columns) is enclosed within parentheses. The columns themselves are separated by commas. This particular table is made up of nine columns. Each column definition starts with the column name (which must be unique within the table), followed