#162 Can't insert foreign key NULL

closed-fixed
Jakub Vrána
MySQL (66)
5
2011-06-23
2011-06-21
Anonymous
No

Using adminer editor on a (InnoDB) table with foreign key having attribute ON DELETE SET NULL it is not possible to insert a record with a NULL value for this foreign key field.

So this is a problem for 'optional 1-n relations'.

Discussion

  • Jakub Vrána
    Jakub Vrána
    2011-06-21

    • status: open --> closed-works-for-me
     
  • Jakub Vrána
    Jakub Vrána
    2011-06-21

    If the field allows NULL (it isn't NOT NULL) then you can insert a record with a NULL value by selecting nothing in the foreign key option list.

     

  • Anonymous
    2011-06-21

    Hi Jakub,

    Using Adminer there is no problem inserting a record having null value for the foreign key. (the field allows NULL).

    But Editor complains about the foreign key constraint when selecting nothing from the option list and insert.

    So we are experiencing different behaviour. Any clue?

    Regards,
    Frans

     
  • Jakub Vrána
    Jakub Vrána
    2011-06-22

    Please attach a dump and describe the table and column which Editor is complaining about.

     
  • Jakub Vrána
    Jakub Vrána
    2011-06-22

    • status: closed-works-for-me --> pending-works-for-me
     

  • Anonymous
    2011-06-23

    parent-child SQL-dump

     
    Attachments

  • Anonymous
    2011-06-23

    Please see attached files.

     
  • Jakub Vrána
    Jakub Vrána
    2011-06-23

    The problem is in the varchar ID. There is no option to differentiate between the NULL value and an empty string in <select>. The solution would be to create another <select> or checkbox just for selecting the empty value but it is not very user friendly. Do you have another idea?

     
  • Jakub Vrána
    Jakub Vrána
    2011-06-23

    • status: pending-works-for-me --> open-accepted
     

  • Anonymous
    2011-06-23

    Translating the empty string to NULL would be a solution that is intuitive and comparable to INT keys.

    Emptystring as a referenced key value is a bad idea anyway. If that is realy what you want you can use adminer for these inserts.

     
  • Jakub Vrána
    Jakub Vrána
    2011-06-23

    Thank you for the report, I've fixed it in Git. You can download the "Current development version" from http://www.adminer.org/en/#download

     
  • Jakub Vrána
    Jakub Vrána
    2011-06-23

    • status: open-accepted --> closed-fixed
     
  • Jakub Vrána
    Jakub Vrána
    2011-06-23

    I agree. I've changed the behavior so that now it is not possible to link empty-string foreign key in the NULL field.

     

  • Anonymous
    2011-06-24

    Jakub, thank you very much for this quick fix.