Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2013
    Posts
    3

    Unanswered: MYSQL: How to insert NULL values in Foreign Key field

    How can I insert a null value in foreign key field using MySql database

    I have the following data model and environment is, Hibernate 3.0(with annotations) and MySQL 5.5

    DB Model:
    Create table ROLE(
    role_id bigint(10) auto_increment,
    primary key(role_id)
    );
    Create table user(
    userId bigint(10) not null auto_increment,
    role_id bigint(10) null,
    Primary key(userid),
    Constraint fk1 foreign key(role_id) references ROLE(role_id)
    );

    I am trying to insert a USER record in database and leaving the role field empty though my role field references ROLE.ROLE_ID. While creating the User I will not have Role information. I am using Hibernate 3.0 with annotations and it is throwing error when I leave USER.ROLE_ID field empty.

    Is there a way to leave foreign key blank/insert null values in it?

  2. #2
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    What happened when you tried?
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  3. #3
    Join Date
    Apr 2013
    Posts
    3
    I am getting foreign key constraint error. when I remove the constraint, it works fine.

    Any tips to handle this?

  4. #4
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    When you say you are leaving the roleid empty. Can you show us the INSERT statement that you executed. This should work. I suspect that instead of it being a NULL you are inserting some value which forces it to validate against the ROLE table.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  5. #5
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by webuser View Post
    I am getting foreign key constraint error. when I remove the constraint, it works fine.

    Any tips to handle this?
    Works as expected: SQL Fiddle
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  6. #6
    Join Date
    Apr 2013
    Posts
    1

    How to insert null in bigint field using hibernate

    In extension to above problem....the problem appears to be, hibernate inserting some default value and which is not accepted by the DB FK constraint.

    My Next question is,

    How can I insert Null value using hibernat3 and annotations....

    My Model attribute for role_id is long.
    I can not explicitly assign null to primitive long....like String data type.....

    Right now, I am not assigning any value before save and thus talking some weired value....

    Any help in assigning null to a long primitive type in model class....

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •