I am trying to add a foreign key to an existing table like this...
alter table expeditedaddress add foreign key
FK_ExpeditedAddress_Brokerage_ID(brokerageId) references brokerage(id);
And get the following error
[2011-10-05 14:54:28] [42S01][1050] Ta开发者_JAVA技巧ble '.\realtorprint_dev\expeditedaddress' already exists
[2011-10-05 14:54:28] [HY000][1025] Error on rename of '.\realtorprint_dev\#sql-20a4_17' to '.\realtorprint_dev\expeditedaddress' (errno: -1)
[2011-10-05 14:54:28] [42S01][1050] Table '.\realtorprint_dev\expeditedaddress' already exists
For clarity's sake her is the show create table for the two tables which are involved...
CREATE TABLE `brokerage` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`name` varchar(255) CHARACTER SET latin1 NOT NULL,
`agency_id` bigint(20) NOT NULL,
PRIMARY KEY (`id`),
KEY `idx_Brokerage_agency_id` (`agency_id`),
CONSTRAINT `FK_Brokerage_agency_id` FOREIGN KEY (`agency_id`) REFERENCES `agency` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8;
CREATE TABLE `expeditedaddress` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`description` varchar(255) CHARACTER SET latin1 NOT NULL,
`address` varchar(255) CHARACTER SET latin1 NOT NULL,
`city` varchar(255) CHARACTER SET latin1 NOT NULL,
`country` varchar(255) CHARACTER SET latin1 NOT NULL,
`stateProvince` varchar(255) CHARACTER SET latin1 NOT NULL,
`zipPostal` varchar(255) CHARACTER SET latin1 NOT NULL,
`brokerageId` bigint(20) NOT NULL,
`timezoneCode` varchar(100) NOT NULL,
PRIMARY KEY (`id`),
KEY `country` (`country`,`stateProvince`),
CONSTRAINT `ExpeditedAddress_ibfk_1` FOREIGN KEY (`country`, `stateProvince`) REFERENCES `stateprovince` (`countryName`, `name`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
精彩评论