Altering Tables

Altering a table is the process of making changes to an existing table. Exercise caution when modifying tables. The database usually tries to save any existing data, but if the modification involves changes that are not compatible, data will be lost. For example, if you have your prices stored as REAL numbers and decide to change them to INT (integers), you will either lose the data after the decimal or lose the data entirely!

Let's say we need to make some changes to the tables we've created in the previous section. We need to add a Size field to the products table, add an Address2 field to the vendors table, and change the Item field in the products database to 100 characters instead of 50.

Here is how you go about making the preceding changes:

01: ALTER TABLE products ADD Size VARCHAR(20); 02: ALTER TABLE vendors ADD Address2 VARCHAR(100); 03: ALTER TABLE products MODIFY Item VARCHAR(100);

Lines 1-2 add a new column.

Line 3 modifies the Item column; it sets the new length to 100 instead of the 50 that it used to be.

Modifying the tables is something you should do only if you absolutely must. Try to design your tables properly in the first place, and you won't need to modify them later. By modifying an existing table with data in it, you risk losing data. If you have data in a table already, make sure you back the database table up before trying to modify it.