Why does my foreign key constraint fail when it is NULL?

I have a table ‘clients’ which records the registration information for new clients. One of the fields is ‘careplanner_id’ which is smallint(5), NULL, with a default of NULL. It is a foreign key pointing to the field ‘id’ in the ‘users’ table. This is also set to smallint(5).

When I test the registration form for a client who has not yet been assigned a careplanner (so the careplanner_id form field is left blank), I get the following error message:

“Cannot add or update a child row: a foreign key constraint fails
(rc_crm.clients, CONSTRAINT clients_ibfk_1
FOREIGN KEY (careplanner_id) REFERENCES users (id) ON DELETE SET NULL ON UPDATE CASCADE)”.

What could be causing this error? Is it because there is no matching ‘id’ in the ‘users’ table? If so, how can I deal with this field sometimes being left blank?

a nullable FK is perfectly fine

i’d want to see the actual INSERT or UPDATE statement

Here is the full error message and insert statement (I am using CodeIgniter 3 as my PHP framework):

key constraint fails (`rc_crm`.`clients`, CONSTRAINT `clients_ibfk_1` 
FOREIGN KEY (`careplanner_id`) REFERENCES `users` (`id`) ON DELETE SET 
NULL ON UPDATE CASCADE)INSERT INTO `clients` (`account_num`, 
`title`, `first_name`, `middle_name`, `last_name`, `age`, `sex`, 
`guidelines`, `guidelines_signed`, `signed_date`, `home_address1`, 
`home_address2`, `home_city`, `home_prov`, `home_postal`, `home_phone`, 
`phone_msg`, `cell_phone`, `work_phone`, `work_ext`, `email`, 
`home_mail`, `mail_address1`, `mail_address2`, `mail_city`, `mail_prov`,
 `mail_postal`, `contact_notes`, `inquiry_type`, `other_inquiry_type`, 
`ask_notes`, `visit_date`, `time_spent`, `assignment_date`, 
`coordinator_id`, `referred_by`, `repeat_client`, `repeat_notes`, 
`careplanner_id`, `case_status`, `status_change_date`, `church_id`) 
VALUES ('16-00023-JD', 'Mr', 'John', '', 'Doe', '24', 'Male', 'yes', 
NULL, '', '12 Alfred Ave.', '', 'Unionville', 'ON', '', '', 'no', '', 
'', '', '', 'yes', '', '', '', 'ON', '', '', 'General Counselling', '', 
'', '2016-01-07', '25', '', '3', '', 'no', '', '', 'Active', NULL, '1') 

One thing I noticed is that careplanner_id is empty, instead of being given a value of NULL. Is that a problem?

Yes, NULL is not an empty string.

actually, in this case it’s the other way around – an empty string is not null

what’s happening is that a string – any string – is not a valid data value for an integer column

other databases would kick this out as an error, but mysql, in its infinite wisdom (yes, that’s sarcasm) decides to change the string to a 0

and 0 is presumably not a valid user id value, which i’m guessing is an auto_increment – and auto_increments cannot be 0, they always start at 1

so since there’s no user id 0, you get the foreign key error

makes sense?

“best practice” requires you to observe the actual data type of the values you want to insert, and don’t use strings for integer columns when NULL is what you really want

Yes, but when I built the database, I set this field to be NULL, with a default value of NULL. So why is the insert giving me an empty string instead of NULL? And is NULL not allowed with integer values?

who wrote the insert statement? whoever did, decided to use an empty string – a non-NULL value – where clearly a NULL is required… the syntax would be exactly like the value you’re inserting for status_change_date

of course it is – but you have to actually say NULL, and not use an empty string

So I have to dig into CodeIgniter 3 to see how it handles empty form fields, because this is my insert function in the model:

    /*
    *    create a new client
    */
    public function create_client($client_data) {
          $query = $this->db->insert('clients', $client_data);
          return true;   
    }

Sorry I’ve been busy over the past few days. I’m still trying to clear this up - one last attempt.

Here is my table structure:

CREATE TABLE IF NOT EXISTS `clients` (
  `id` int(10) unsigned NOT NULL,
  `account_num` varchar(15) NOT NULL,
  `careplanner_id` smallint(5) unsigned DEFAULT NULL,
  `coordinator_id` smallint(5) unsigned NOT NULL,
  `church_id` smallint(4) unsigned NOT NULL,
  `inquiry_type` varchar(50) DEFAULT NULL,
  `case_status` varchar(50) NOT NULL,
  `title` varchar(10) DEFAULT NULL,
  `first_name` varchar(30) NOT NULL,
  `middle_name` varchar(30) DEFAULT NULL,
  `last_name` varchar(30) NOT NULL,
  `age` int(3) DEFAULT NULL,
  `sex` varchar(10) DEFAULT NULL,
  `guidelines` varchar(3) NOT NULL,
  `guidelines_signed` varchar(3) DEFAULT 'no',
  `home_address1` varchar(100) DEFAULT NULL,
  `home_address2` varchar(100) DEFAULT NULL,
  `home_city` varchar(50) DEFAULT NULL,
  `home_prov` varchar(2) DEFAULT NULL,
  `home_postal` varchar(7) DEFAULT NULL,
  `home_phone` varchar(15) DEFAULT NULL,
  `phone_msg` varchar(3) DEFAULT NULL,
  `cell_phone` varchar(15) DEFAULT NULL,
  `work_phone` varchar(15) DEFAULT NULL,
  `work_ext` varchar(10) DEFAULT NULL,
  `email` varchar(100) DEFAULT NULL,
  `home_mail` varchar(3) NOT NULL,
  `mail_address1` varchar(100) DEFAULT NULL,
  `mail_address2` varchar(100) DEFAULT NULL,
  `mail_city` varchar(50) DEFAULT NULL,
  `mail_prov` varchar(2) DEFAULT NULL,
  `mail_postal` varchar(7) DEFAULT NULL,
  `contact_notes` text NOT NULL,
  `other_inquiry_type` varchar(100) DEFAULT NULL,
  `ask_notes` text,
  `visit_date` datetime DEFAULT CURRENT_TIMESTAMP,
  `time_spent` tinyint(3) NOT NULL,
  `referred_by` varchar(255) DEFAULT NULL,
  `repeat_client` varchar(8) DEFAULT NULL,
  `repeat_notes` text,
  `assignment_date` datetime NOT NULL,
  `status_change_date` datetime DEFAULT NULL,
  `signed_date` datetime DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `modified_at` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB AUTO_INCREMENT=23 DEFAULT CHARSET=latin1;

As you can see, I have careplanner_id set with NULL as its DEFAULT. Why is it being changed to an empty string?

Would it be better to just give up, remove the foreign key and deal with changes manually with my php code. In other words, if a careplanner is removed from the list, just do a search for all clients with that careplanner_id, and replace the id number with an empty string? Or should I just check for the empty field, and manually set it to NULL? Something is not working with my database DEFAULT NULL.

I don’t think the problem is with DEFAULT NULL, as Rudy said, an empty string is not a NULL. And DEFAULT is only if nothing else is there.

I’m wondering how a smallint(5) field could accept anything other a smallint without an error happening during the attempt.

So when I submit the form without selecting a careplanner for the client (it could be the coordinator want to assign the careplanner later), the value submitted will always be an empty string because it is coming from a select, never NULL.

I might be nitpicking a bit here, but in this situation, would it be best to have the value of the ‘non-selection’ in the dropdown be NULL or an empty string?

Out of frustration, I ended up changing the DEFAULT to 0 and removing the FK constraint. I will just code in a check to change all instances of careplanner_id to 0 if a particular careplanner is removed, so the client will revert to being unassigned.

[quote=“Mittineague, post:10, topic:212620, full:true”]I’m wondering how a smallint(5) field could accept anything other a smallint without an error happening during the attempt.
[/quote]

because “By default, MySQL is forgiving of invalid or improper data values and coerces them to valid values for data entry” – https://dev.mysql.com/doc/refman/5.6/en/constraint-invalid-data.html

so, as we’ve seen in this thread, if you try to insert a non-null empty string into an integer column, you get a 0 instead

1 Like

this is sad, and you should feel sad

the correct approach is to keep the FK, make the column DEFAULT NULL, and then take whatever steps necessary to fix the code generator

as i am not a php or codeigniter developer, i cannot comment on how easy it would be

however, please note that you ~are~ already inserting proper NULL values in your insert statement forat least one date column

why can’t you do the same for the caregiver_id?

Okay. I definitely don’t want to be sad. :blush: I want to do things the proper way, but this project is producing all sorts of little ‘what if’ issues that I have never run across before, and it helps to have a database expert such as you, keep me on the right track in terms of best practices.

I will go back to my DEFAULT NULL with FK constraint and see if I can fix the database INSERT issue.

thank you for doing it the better™ way :innocent:

good luck with the code

as i said, you’re already inserting NULL for status_change_date, so i’m confident you’ll find a way

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.