#162 Can't insert foreign key NULL

closed-fixed
MySQL (68)
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.

     
  • Comment has been marked as spam. 
    Undo

    You can see all pending comments posted by this user  here

    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
     
  • Comment has been marked as spam. 
    Undo

    You can see all pending comments posted by this user  here

    Anonymous - 2011-06-23

    parent-child SQL-dump

     
    Attachments
  • Comment has been marked as spam. 
    Undo

    You can see all pending comments posted by this user  here

    Anonymous - 2011-06-23
     
    Attachments
  • Comment has been marked as spam. 
    Undo

    You can see all pending comments posted by this user  here

    Anonymous - 2011-06-23
     
    Attachments
  • Comment has been marked as spam. 
    Undo

    You can see all pending comments posted by this user  here

    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
     
  • Comment has been marked as spam. 
    Undo

    You can see all pending comments posted by this user  here

    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.

     
  • Comment has been marked as spam. 
    Undo

    You can see all pending comments posted by this user  here

    Anonymous - 2011-06-24

    Jakub, thank you very much for this quick fix.

     

Get latest updates about Open Source Projects, Conferences and News.

Sign up for the SourceForge newsletter:





No, thanks