How can I modify UNIQUE key on this table? 2

[peterv12]

I have a books table that I created using this script:

CREATE table MYBOOKS
(bookid INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
 authorid INT NOT NULL,
 title VARCHAR(60) NOT NULL,
 isbn VARCHAR(13),
 type VARCHAR(20) NOT NULL,
 cover_type VARCHAR(10) NOT NULL,
 pages INT,
 copyright YEAR,
 date_finished DATE,
 has_been_read CHAR(01),
 needed CHAR(01),
 [COLOR=red][b]UNIQUE KEY(title),[/b][/color]
 FOREIGN KEY (authorid) REFERENCES authors (authorid));

What I'd like to be able to do is REMOVE the unique "title" key without having to drop and then recreate the table. Currently the data in the table has NO duplicate titles. I would then like to add a UNIQUE key that is a combination of the title and authorid fields. Can anyone tell me if what I'm attempting to do is possible? A description of the table follows:

mysql> desc mybooks;
+---------------+-------------+------+-----+---------+----------------+
| Field         | Type        | Null | Key | Default | Extra          |
+---------------+-------------+------+-----+---------+----------------+
| bookid        | int(11)     | NO   | PRI | NULL    | auto_increment |
| authorid      | int(11)     | NO   | MUL | NULL    |                |
| [COLOR=red][b]title[/b][/color]         | varchar(60) | NO   | [b][COLOR=red]UNI[/color][/b] | NULL    |                |
| isbn          | varchar(13) | YES  |     | NULL    |                |
| type          | varchar(20) | NO   |     | NULL    |                |
| cover_type    | varchar(10) | NO   |     | NULL    |                |
| pages         | int(11)     | YES  |     | NULL    |                |
| copyright     | year(4)     | YES  |     | NULL    |                |
| date_finished | date        | YES  |     | NULL    |                |
| has_been_read | char(1)     | YES  |     | NULL    |                |
| needed        | char(1)     | YES  |     | NULL    |                |
+---------------+-------------+------+-----+---------+----------------+
11 rows in set (0.00 sec)

Any suggestions would be greatly appreciated.

 

[vacunita]

You cna use the alter table command.

Try something like:

alter table mytablename DROP KEY myuniquekey, ADD UNIQUE KEY mynewkey (Supplier,validfrom);

MYSQL Documentation: Alter Table

----------------------------------
Phil AKA Vacunita
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[DonQuichote]

In the above statement, you should know the name of the index. The query:

SHOW INDEX FROM MYBOOKS

should tell you what it is called. This is the index to drop in an ALTER TABLE statement.

+++ Despite being wrong in every important aspect, that is a very good analogy +++
Hex (in Darwin's Watch)

 

[peterv12]

Thanks guys, these tips really help!