Jump to content
Sign in to follow this  
jaylow

foreign key error

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

Share this post


Link to post
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.

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
Sign in to follow this  

×
×
  • Create New...