Learning Mysql

mysql> ALTER TABLE staff ADD PRIMARY KEY (name);
ERROR 1068 (42000): Multiple primary key defined mysql> ALTER TABLE staff DROP PRIMARY KEY;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates 0 Warnings 0
mysql> ALTER TABLE staff ADD PRIMARY KEY (name);
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates 0 Warnings 0
MySQL complains when we try to add the second primary key on name we have to drop the existing primary key on staff_id
, and then add one on name
You can’t modify an index once it’s been created. However, sometimes you’ll want to;
for example, you might want to reduce the number of characters indexed from a column or add another column to the index. The best method to do this is to drop the index and then create it again with the new specification. For example, suppose you decide that you want the by_name index to include only the first 10 characters of the artist_name
. Simply do the following:
mysql> ALTER TABLE artist DROP INDEX by_name;
Query OK, 6 rows affected (0.02 sec)
Records: 6 Duplicates 0 Warnings 0
mysql> ALTER TABLE artist ADD INDEX by_name (artist_name(10));
Query OK, 6 rows affected (0.03 sec)
Records: 6 Duplicates 0 Warnings 0
Renaming Tables and Altering Other Structures
We’ve seen how to modify columns and indexes in a table now let’s see how to modify tables themselves. It’s easy to rename a table. Suppose you want to rename played to playlist. Use the following command:
mysql> ALTER TABLE played RENAME TO playlist;
Query OK, 0 rows affected (0.00 sec)
The
TO
keyword is optional.
There are several other things you can do with
ALTER
statements:
• Change the default character set and collation order fora database, a table, or a column Change the order of the rows in a table. This is useful only if you know you want to access the rows in a particular order and you want to help get the data into or near that order Manage and change constraints. For example, you can add and remove foreign keys.

Download 4.24 Mb.

Share with your friends: