F 5/29/02 3:40 PM Page 185
Chapter 9 ✦ SQL According to MySQL
185
CREATE TABLE manufacturer (
ID int NOT NULL PRIMARY KEY,
name varchar(50),
address varchar(50),
zip varchar(10),
area_code char(3),
telephone_number char(7),
contact_name varchar(50)
);
CREATE TABLE product (
ID int NOT NULL PRIMARY KEY,
name varchar(50),
price decimal(9,2),
quantity integer,
manu_ID int,
cate_ID int
);
CREATE TABLE producttype (
ID int NOT NULL PRIMARY KEY,
category varchar(50)
);
On the
The table layout is available in electronic format on the CD-ROM so you can CD-ROM
easily import the design into MySQL.
Using the table layout from the CD-ROM, you can copy and paste tables into the MySQL CLI. Alternatively, you could read the file through the CLI directly (though doing so is beyond the scope of this chapter).
Cross-
In Chapter 10 I’ll show examples of importing data and definitions into MySQL.
Reference
In Figure 9-13, I take a copy of the table definitions and paste them into the CLI. As you can see the commands complete successfully. By producing statements in another application and saving them, you always have a quick and easy alternative to re-creating or redoing those statements from scratch.
Altering tables
Inevitably there comes in time in all databases’ lives when their tables will need to be altered. This can happen for any number of reasons — including a change in business rules, an upgrade to an application, or an oversight during the design of the database. The syntax for the ALTER TABLE statement is as follows: ALTER [IGNORE] TABLE tbl_name alter_spec [, alter_spec ...]
alter_specification:
ADD [COLUMN] create_definition [FIRST | AFTER
column_name ]
or ADD [COLUMN] ( create_definition, create_definition,...) or ADD INDEX [index_name] ( index_col_name,...)
124932-4 ch09.F 5/29/02 3:40 PM Page 186
186
Part II ✦ SQL Essentials
or ADD PRIMARY KEY ( index_col_name,...)
or ADD UNIQUE [ index_name] ( index_col_name,...) or ADD FULLTEXT [ index_name] ( index_col_name,...) or ADD [CONSTRAINT symbol] FOREIGN KEY index_name ( index_col_name,...)
[ reference_definition]
or ALTER [COLUMN] col_name {SET DEFAULT literal | DROP
DEFAULT}
or CHANGE [COLUMN] old_col_name create_definition
or MODIFY [COLUMN] create_definition
or DROP [COLUMN] col_name
or DROP PRIMARY KEY
or DROP INDEX index_name
or DISABLE KEYS
or ENABLE KEYS
or RENAME [TO] new_tbl_name
or ORDER BY col
or table_options
As you can see, this is quite a complex statement with many options (some of which are beyond the scope of this chapter).
Cross-
In Appendix A you’ll find more information on the ALTER TABLE statement.
Reference
Behind the scenes, the ALTER TABLE statement makes a temporary copy of the table and then performs the change on the new table before deleting the old table.
The only exception to this behavior is the RENAME function, which does not create a temporary copy of the table. Any updates made while an ALTER TABLE statement is being performed will be made on the temporary copy and thus not lost.
Figure 9-13: Pasting statements into the CLI is a good way to increase productivity.
124932-4 ch09.F 5/29/02 3:40 PM Page 187
Chapter 9 ✦ SQL According to MySQL
187
MySQL offers some extensions to the ANSI92 SQL standard with the ALTER TABLE
statement. Specifically, this includes the IGNORE keyword, which causes MySQL to ignore any duplicate values in a primary key or in a unique column type. When a duplicate value is found, MySQL only uses the first instance of that value. MySQL
also enables multiple alterations to be performed in the same ALTER TABLE
statement.
Many table alterations involve changing a column type or adding a column. Just as common for the database administrator, however, are tasks such as renaming a table and adding indexes.
For example, Figure 9-14 alters the Customer table to add a column. The figure shows the table structure before and after this process, via a DESCRIBE statement (explained earlier in this chapter).
Figure 9-14: The Customer table layout before an ALTER TABLE
statement, the ALTER TABLE statement, and the table structure after the ALTER TABLE statement
Because I don’t want the Country column to be added to the Customer table, I have a good opportunity to demonstrate how to delete or drop a column from a table. Figure 9-15 describes the table structure both before and after the ALTER
TABLE statement drops a column.
124932-4 ch09.F 5/29/02 3:40 PM Page 188
188
Part II ✦ SQL Essentials
Figure 9-15: The Customer table layout with the extra column both before and after the column is dropped