**SOLVED: Just had to make sure there was valid data in both table columns before assigning the foreign key. Silly mistake!
------------------
Yeah I decided to add a new table with a primary key, and create a new field and foreign key reference in an existing table (both InnoDB) - now the field insertion into the existing table worked, but everytime I try and update the fields foreign key I get the following error:
cannot add or update a child row: foreign key constraints #sql-bd0-21
Both field attributes are the same (type, size, Not Null etc.) and no matter what I try, I can't seem to get the foreign key to work.
In host_games table I would like the currency_id to be a foreign key referencing the currency_id in currency_id table.
host_games table dump:
Code:
CREATE TABLE `host_games` (
`game_id` int(11) unsigned NOT NULL,
`host_id` int(11) unsigned NOT NULL,
`location_id` int(4) unsigned NOT NULL,
`setup_fee` int(11) unsigned NOT NULL,
`web_hosting` int(11) unsigned NOT NULL,
`currency_id` int(11) unsigned NOT NULL,
`ftp_access` int(11) unsigned NOT NULL,
`redirect_sys` int(11) unsigned NOT NULL,
`public_slot` decimal(6,2) NOT NULL,
`private_slot` decimal(6,2) NOT NULL,
`date_added` date NOT NULL,
`last_updated` date DEFAULT NULL,
PRIMARY KEY (`game_id`,`host_id`,`location_id`),
KEY `game_id` (`game_id`),
KEY `host_id` (`host_id`),
KEY `location_id` (`location_id`),
KEY `setup_fee` (`setup_fee`),
KEY `redirect_sys` (`redirect_sys`),
KEY `ftp_access` (`ftp_access`),
KEY `currency_id` (`currency_id`),
CONSTRAINT `ftp_access_fk` FOREIGN KEY (`ftp_access`) REFERENCES `options` (`options_id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `host_gamesid_fk` FOREIGN KEY (`game_id`) REFERENCES `games` (`game_id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `host_id_fk` FOREIGN KEY (`host_id`) REFERENCES `host` (`host_id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `location_id_fk` FOREIGN KEY (`location_id`) REFERENCES `location` (`location_id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `redirect_sys_fk` FOREIGN KEY (`redirect_sys`) REFERENCES `options` (`options_id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `setup_fee_fk` FOREIGN KEY (`setup_fee`) REFERENCES `options` (`options_id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `web_hosting_fk` FOREIGN KEY (`ftp_access`) REFERENCES `options` (`options_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
currency_id table dump:
Code:
CREATE TABLE `currency` (
`currency_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`currency` varchar(3) NOT NULL,
PRIMARY KEY (`currency_id`),
UNIQUE KEY `currency` (`currency`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
The only difference I can spot, is that in the currency_id table, currency_id auto increments - but that didnt cause a problem with the location_id foreign key. Any help appreciated - I've searched google but found nothing as specific as what I'm trying to figure out!
dBforums
>
Foreign Key Constraint Error (inserting new field)
03-25-10, 13:39
Linear Mode
