• Choose a category
• All
• Classic-Fiction

* * *

Caution: Foreign Keys Can’t Span Engines

There is one big downside to mixing engine types. Foreign keys (used to enforce referential integrity, as explained in Chapter 1) cannot span engines. That is, a table using one engine cannot have a foreign key referring to a table that uses another engine.

* * *

So, which should you use? Well, that depends on what features you need. ARIA is new to MariaDB and provides the ideal combination of performance and features. But, if you do need to use features in other engines, know that the option of doing so is available to you.

Updating Tables

To update table definitions, the ALTER TABLE statement is used. But, ideally, tables should never be altered after they contain data. You should spend sufficient time anticipating future needs during the table design process so extensive changes are not required later on.

To change a table using ALTER TABLE, you must specify the following information:

• The name of the table to be altered after the keywords ALTER TABLE. (The table must exist or an error will be generated.)

• The list of changes to be made.

The following example adds a column to a table:

Input

ALTER TABLE vendors

ADD vend_phone CHAR(20);

Analysis

This statement adds a column named vend_phone to the vendors table. The datatype must be specified.

To remove this newly added column, you can do the following:

Input

ALTER TABLE Vendors

DROP COLUMN vend_phone;

One common use for ALTER TABLE is to define foreign keys. The following is the code used to define the foreign keys used by the tables in this book:

ALTER TABLE orderitems

ADD CONSTRAINT fk_orderitems_orders

FOREIGN KEY (order_num) REFERENCES orders (order_num);

ALTER TABLE orderitems

ADD CONSTRAINT fk_orderitems_products FOREIGN KEY (prod_id)

REFERENCES products (prod_id);

ALTER TABLE orders

ADD CONSTRAINT fk_orders_customers FOREIGN KEY (cust_id)

REFERENCES customers (cust_id);

ALTER TABLE products

ADD CONSTRAINT fk_products_vendors

FOREIGN KEY (vend_id) REFERENCES vendors (vend_id);

Here four ALTER TABLE statements are used, as four different tables are being altered. To make multiple alterations to a single table, a single ALTER TABLE statement could be used with each of the alterations specified comma delimited.

Complex table structure changes usually require a manual move process involving these steps:

1. Create a new table with the new column layout.

2. Use the INSERT SELECT statement (see Chapter 19, “Inserting Data,” for details of this statement) to copy the data from the old table to the new table. Use conversion functions and calculated fields, if needed.

3. Verify that the new table contains the desired data.

4. Rename the old table (or delete it, if you are really brave).

5. Rename the new table with the name previously used by the old table.

6. Re-create any triggers, stored procedures, indexes, and foreign keys as needed.

* * *

Caution: Use ALTER TABLE Carefully

Use ALTER TABLE with extreme caution, and be sure you have a complete set of backups (both schema and data) before proceeding. Database table changes cannot be undone—and if you add columns you don’t need, you might not be able to remove them. Similarly, if you drop a column that you do need, you might lose all the data in that column.

* * *

Deleting Tables

Deleting tables (actually removing the entire table, not just the contents) is easy—arguably too easy. Tables are deleted using the DROP TABLE statement:

Input

DROP TABLE customers2;

Analysis

This statement deletes the customers2 table (assuming it exists). There is no confirmation, nor is there an undo—executing the statement permanently removes the table.

Renaming Tables

To rename a table, use the RENAME TABLE statement as follows:

Input

RENAME TABLE customers2 TO customers;

Analysis

RENAME TABLE does just that, it renames a table. Multiple tables may be renamed in one operation using the syntax:

RENAME TABLE backup_customers TO customers,

backup_vendors TO vendors,

backup_products