Jump to content

foreign key error


jaylow

Recommended Posts

Hello, i have 2 tables. one is for users to sign up and the other is to make character.

i added a foreign key to the character table to link it to the members table

 

not when i sign up it just adds me to the table but when i want to make a character it give me this error

i want to link the character_id to the id in the members table, so i know what character belongs to what member..

 

 

i get:

 

ERROR:could not able to execute INSERT INTO `character` (charname, ######, gold, xp, accdate) VALUES ('jay', 'male', 100000, 0, now()).Cannot add or update a child row: a foreign key constraint fails (`secure_login`.`character`, CONSTRAINT `character_ibfk_1` FOREIGN KEY (`character_id`) REFERENCES `members` (`id`) ON DELETE CASCADE ON UPDATE CASCADE)

 

 

Members table:

CREATE TABLE IF NOT EXISTS `members` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `username` varchar(30) NOT NULL,  `email` varchar(50) NOT NULL,  `password` char(128) NOT NULL,  `salt` char(128) NOT NULL,  `accdate` timestamp NOT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;

`character` table:

CREATE TABLE IF NOT EXISTS `character` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `character_id` int(11) NOT NULL,  `charname` varchar(30) NOT NULL,  `######` tinyint(4) NOT NULL,  `gold` int(11) NOT NULL,  `xp` int(11) NOT NULL,  `accdate` timestamp NOT NULL,  PRIMARY KEY (`id`),  KEY `character_id` (`character_id`)) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;---- Constraints for table `character`--ALTER TABLE `character`  ADD CONSTRAINT `character_ibfk_1` FOREIGN KEY (`character_id`) REFERENCES `members` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

without a foreign key i can update the characters table without any issue

i messed around with the tables for the last two hours and just dont know anymore.

if i switch the foreign key to my members table the sign up does not go into the members table

Link to comment
Share on other sites

INSERT INTO `character` (charname, gender, gold, xp, accdate) VALUES ('jay', 'male', 100000, 0, now()).

Since you have a foreign key constraint you will need to insert the character_id value and that value must match one of the id values in the member table. You also have a not-null constraint and that is also being violated by the omission of character_id.

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...