MySQL: Problem with a specific reference
I have two tables. On is a table that contains IP Ranges and their respective country attributions (IPGEO table). The other is a table which simply keeps track of the last country the site was accessed from on a per user basis. The idea is that, if the user suddenly accesses the site from another country, I notify the user about this via email.
Now for the actual tables. I have these two:
The IPGeo table that contains the IP ranges
CREATE TABLE IF NOT EXISTS `politiker_lu`.`IPGeo` (
`IPFrom` INT(11) NOT NULL ,
`IPTo` INT(11) NOT NULL ,
`code2` VARCHAR(2) NOT NULL ,
`code3` VARCHAR(3) NOT NULL ,
`Country` VARCHAR(45) NOT NULL ,
INDEX `index1` (`IPFrom` ASC) ,
INDEX `index2` (`IPTo` ASC) )
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;
Then, there is the user_geo table that tracks the last country from which the user accessed the site.
CREATE TABLE IF NOT EXISTS `politiker_lu`.`user_geo` (
`fi_user` INT(10) UNSIGNED NOT NULL ,
`fi_country` VARCH开发者_JAVA百科AR(3) NOT NULL ,
`timestamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ,
PRIMARY KEY (`fi_user`) ,
INDEX `fk_user_geo_1` (`fi_user` ASC) ,
CONSTRAINT `fk_user_geo_1`
FOREIGN KEY (`fi_user` )
REFERENCES `politiker_lu`.`user` (`id_user` )
ON DELETE CASCADE
ON UPDATE CASCADE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;
These are the tables as they exist. I now want to reference these two tables as follows:
ALTER TABLE `politiker_lu`.`user_geo`
ADD CONSTRAINT `fk_user_geo_IPGeo1`
FOREIGN KEY (`fi_country` )
REFERENCES `politiker_lu`.`IPGeo` (`code3` )
ON DELETE CASCADE
ON UPDATE CASCADE
, ADD INDEX `fk_user_geo_IPGeo1` (`fi_country` ASC) ;
That Statement however fails with errno 150. Both tables are utf8, both columns have the same data-type. Am I missing something vital here?
Notes
The table user
exists and has all the references and is actually irrelevant to the problem. I left it so that I didn't need to edit the statement too much.
You need to add a unique index or a primary key on the IPGeo.code3 column in order to reference it with a foreign key.
You can see the error by running show engine innodb status\G
and looking under the LATEST FOREIGN KEY ERROR
section. The error probably looks something like this:
Cannot find an index in the referenced table where the
referenced columns appear as the first columns, or column types
in the table and the referenced table do not match for constraint.
Note that the internal storage type of ENUM and SET changed in
tables created with >= InnoDB-4.1.12, and such columns in old tables
cannot be referenced by such columns in new tables.
See http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html
for correct foreign key definition.
精彩评论