How to pass a null value from HTML form to mysql db foreign key field

Question

I have two tables: parent and child. One of the child column references parent PRIMARY KEY with FOREIGN KEY constraint - child.parent_id references parent.parent_id.

Foreign key column in child tables allows NULLs values because when child will be created parent may be not known yet. But when info about parent will be assigned/updated i want to have referential integrity.

And now the question:

How to pass null value from HTML form to MySQL? Quotes '' or "" not working "foreign key constraint fails" probably because it passes empty string instead of null value. Or maybe do I need to do some additional checking in PHP and convert it to null ?

CREATE TABLE IF NOT EXISTS `parent` (
  `parent_id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  `surname` varchar(20) NOT NULL,
  PRIMARY KEY (`parent_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `child` (
 `child_id` int(11) NOT NULL AUTO_INCREMENT,
 `child_name` varchar(20) NOT NULL,
 `parent_id` int(11) DEFAULT NULL,
 PRIMARY KEY (`child_id`),
 KEY `fk_parent_id` (`parent_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

ALTER TABLE `child`
  ADD CONSTRAINT `fk_parent_id` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`parent_id`);

Michael Sivolobov · Accepted Answer · 2013-06-21 09:31:42Z

0

You need to convert your empty value to null or edit your query in PHP.

answered Jun 21, 2013 at 9:31

Michael Sivolobov

13.9k5 gold badges48 silver badges68 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Darren · Accepted Answer · 2013-06-21 09:31:58Z

0

Why do you want to pass up a NULL value when your tables do not support NULL values in the parent? Also

`parent_id` int(11) DEFAULT NULL,

Should be NOT NULL as a child should always have a parent.

If you are trying to pass parent/child identifiers from a HTML form, I would recommend validating them server side in your PHP code. As I could easily manipulate the values in the markup and add any parent/child combination I wish.

answered Jun 21, 2013 at 9:31

Darren

71.2k24 gold badges141 silver badges146 bronze badges

5 Comments

czomberzdaniela Over a year ago

I want to allow null value in the child table because the parent may still be unknown during child creation and the relation will be created later. I've omitted validation for sake of simplicity and validation won't convert it.

Darren Over a year ago

@czomberzdaniela - you can't do that. You have a foreign key constraint that states that parent_id in the child table must reference an ID in the parent table. If you create a child record without a valid parent id then the constraint will be violated.

czomberzdaniela Over a year ago

Yes You can. INSERT INTO foreign_test.child (child_id, child_name, parent_id) VALUES (NULL, 'Jack', NULL); works fine. You can also do this from phpMyAdmin.

Darren Over a year ago

@czomberzdaniela - does foreign test have a foreign key on parent_id that is referencing the parent table?

czomberzdaniela Over a year ago

yes of course: ALTER TABLE child ADD CONSTRAINT fk_parent_id FOREIGN KEY (parent_id) REFERENCES parent (parent_id);

Collectives™ on Stack Overflow

How to pass a null value from HTML form to mysql db foreign key field

2 Answers 2

Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related