The below-shown tables are connected through the xxx field. How can I drop this relation and update it to yyy without re-creating the tables (maybe using ALTER)?
So, I want that yyy in table1 would be the Key, while xxx becomes just a regular field. In table2 I should probably update yyy to Primary Key.
CREATE TABLE IF NOT EXISTS `table1` (
`id1` smallint(6) NOT NULL AUTO_INCREMENT,
`xxx` smallint(6) DEFAULT NULL,
`yyy` varchar(50) DEFAULT NULL,
PRIMARY KEY (`id1`),
KEY `xxx` (`xxx`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=15;
CREATE TABLE IF NOT EXISTS `table2` (
`xxx` smallint(6) NOT NULL,
`yyy` varchar(50) NOT NULL,
`zzz` varchar(30) NOT NULL,
PRIMARY KEY (`xxx`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;
ALTER TABLE `table1` DROP KEY `xxx`;
ALTER TABLE `table1` ADD KEY `yyy`(`yyy`);
ALTER TABLE `table2` DROP PRIMARY KEY;
ALTER TABLE `table2` ADD PRIMARY KEY (`yyy`);