0

I have two tables that am trying to link, a User table and Registration table:

CREATE TABLE IF NOT EXISTS `users` (
  `id` int(11) NOT NULL auto_increment,
  `first_name` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `password` varchar(32) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=19 ;

CREATE TABLE IF NOT EXISTS `registration` (
  `userid` int(11) NOT NULL,
  `sex` varchar(10) NOT NULL,
  `dob` date NOT NULL,
  `location` varchar(30) NOT NULL,
  `edu_level` varchar(32) NOT NULL,
  `work` varchar(30) NOT NULL,
  `rel_status` varchar(10) NOT NULL,
  KEY `userid` (`userid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

And I created a link between them with the design tab in phpadmin, but when am inserting records into the registration table, it keeps saying this:

Cannot add or update a child row: a foreign key constraint fails

(`letzfuze/registration`, CONSTRAINT `registration_ibfk_1` 
FOREIGN KEY (`userid`) REFERENCES `users` (`id`))

Please who can help?

Isaac Bennetch
  • 11,061
  • 2
  • 28
  • 40
Joshua Lawson
  • 1
  • 2
  • Seeing `password VARCHAR(32)` makes me sad. Are you using [proper password hashing](http://www.phptherightway.com/#password_hashing)? Also `latin1` is usually a bad call. Why not UTF-8? – tadman May 12 '15 at 18:49
  • 1
    @tadman obviously using MD5. – Funk Forty Niner May 12 '15 at 18:50
  • You are trying to "register" a user that does not exist (in `users`). – Uueerdo May 12 '15 at 18:52
  • This is just for a school project of which security is not my main concern. Yes I am trying to register a user who doesn't exist. – Joshua Lawson May 12 '15 at 18:56
  • Does this help ? http://stackoverflow.com/a/21660862/4576237 – koustuv May 12 '15 at 18:58
  • @JoshuaLawson If it's for the purposes of learning, please take the time to learn how to do it properly. It is *literally* a case of making the correct function call. – tadman May 12 '15 at 19:01

1 Answers1

1

The value supplied for the userid column in the registration table MUST ALREADY APPEAR in the id column of a row in users.

The foreign key constraint is essentially saying: "Do not allow a row to be added to the registration table if the value of the userid column is not found as a value of the id column in the users table."

The error message you are getting looks like the behavior we expect when an attempt is made to violate the constraint.

Here's a suggestion:

Insert the new row to users first, get the id value from that row, and then use that value for the userid column in registration.

If you can't do that, you might want to consider using a NULL value for the userid column, or ditching the foreign key constraint and altogether re-thinking your design.

spencer7593
  • 103,596
  • 14
  • 107
  • 133
  • Thats the process actually to first register the user using their Name, email and password, and after that, it takes you to the main registration form where you further register by typing in your sex, dob etc.. but after I hit the submit key, it gives me the error – Joshua Lawson May 12 '15 at 19:01