Solved

How to Insert NULL into MYSQL

Posted on 2011-03-03
24
1,033 Views
Last Modified: 2012-05-11
I have a foreign key field in a tablein MySQL.  its of type BIGINT . This accepts NULL in MySQL.

How Do I insert a Null   into a foreign key field which is of type bigint ?

I'm using PreparedStatement.

I tried this way ....The following code does not work .
....................................
ps.setNull(8, java.sql.Types.INTEGER)
....................................

thorws error

Cannot add or update a child row: a foreign key constraint  fails.  I'm not sure why this error is coming .
0
Comment
Question by:cofactor
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 9
  • 3
  • +2
24 Comments
 
LVL 40

Expert Comment

by:gurvinder372
ID: 35025527
try

 pstmt.setNull(3, java.sql.Types.INTEGER)

 pstmt.setNull(3, java.sql.Types.BIGINT)

http://download.oracle.com/javase/6/docs/api/java/sql/Types.html
0
 
LVL 47

Expert Comment

by:for_yan
ID: 35025536
But if it is a foreign key - can it be NULL? Probaly not
0
The Ultimate Checklist to Optimize Your Website

Websites are getting bigger and complicated by the day. Video, images, custom fonts are all great for showcasing your product/service. But the price to pay in terms of reduced page load times and ultimately, decreased sales, can lead to some difficult decisions about what to cut.

 
LVL 40

Expert Comment

by:gurvinder372
ID: 35025546
i guess you can set null, as long as the child table's foreign key column is not set as NOT NULL
http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html
0
 
LVL 47

Expert Comment

by:for_yan
ID: 35025553
Yes, so maybe it is set NOT NULL - it is worth checking, as the format of inserting NULL seems to have been correct
0
 
LVL 92

Expert Comment

by:objects
ID: 35025615
your code is correct, but you have a constraint in your database that requires it to be set
ie. problems in the database, not your code
0
 
LVL 20

Expert Comment

by:Sathish David Kumar N
ID: 35025651
If its forgien key ....

please reffer any relationship with other table(one to one or one to many ) if its present you cant set the NULL value .
0
 
LVL 20

Expert Comment

by:Sathish David Kumar N
ID: 35025661
That relationship table colum may not be null so it wont allow you insert null value .
0
 

Author Comment

by:cofactor
ID: 35025758
>>>your code is correct, but you have a constraint in your database that requires it to be set

see  script for the foreign key . This allows NULL





foreignkey.png
0
 
LVL 47

Expert Comment

by:for_yan
ID: 35025784
Can you try to insert this row not from java, but from SQL prompt?

If not that then at least try to insert from Java/JDBC but without PreparedStatemnt - at least this experiment
will give some additional information  
0
 

Author Comment

by:cofactor
ID: 35025848
>>>Can you try to insert this row not from java, but from SQL prompt?

plenty !  see the records
 project
0
 

Author Comment

by:cofactor
ID: 35025853
>>>If not that then at least try to insert from Java/JDBC but without PreparedStatemnt - at least this >>>experiment will give some additional information  

Not understood.  Code please
0
 
LVL 47

Expert Comment

by:for_yan
ID: 35025859
can you insert the same command from JDBC without PreparedStatement but with null explicitly
among the values ?
0
 

Author Comment

by:cofactor
ID: 35025901
>>can you insert the same command

I'm using a Editor to test it out but not  INSERT SQL. the SQLyog  GUI  for MYSQL helps  to insert a record

>>>from JDBC without PreparedStatement but with null explicitly
>>>among the values ?
0
 
LVL 47

Expert Comment

by:for_yan
ID: 35025925
Sorry, I never used MySQL - that's why I probably cannot understand.
I thought once you can use PreparedStatemnt in Java code, you should also
be able to use just plain sql without PreparedStatement from the same code.
Myabe it is different with MySQL...
0
 

Author Comment

by:cofactor
ID: 35025984
I have checked the SQL. The SQL insert  query  has NULL  in the foreign key field , then it can insert the record.

Is it possible to avail database  NULL from java ?
0
 
LVL 47

Expert Comment

by:for_yan
ID: 35026053
So, still there is no reason why you need to use PreparedStatement (it may be a bit quicker
on bulk insert, but otherwise...)

Did you try something like that:

Statement stmt = conn.createStatement();
stmt.executeUpdate("insert into table_name (col1_name, col2_nam2,col3_nam3) values ('a',null,'b')

Of course you can use some strings instead of actual values a and b

0
 
LVL 47

Expert Comment

by:for_yan
ID: 35026064
Of course I forgot the closing parenthesis in th eendo of the line
0
 

Author Comment

by:cofactor
ID: 35026097
>>>stmt.executeUpdate("insert into table_name (col1_name, col2_nam2,col3_nam3) values ('a',null,'b')

I can not keep always null  .  

I have this ...

if(project_activity_serial_no==null||"null".equals(project_activity_serial_no)||"".equals(project_activity_serial_no)) {
	
	System.out.println("before setting null "+java.sql.Types.INTEGER);
	ps.setNull(8, java.sql.Types.INTEGER);  // set NULL
	System.out.println("after setting null");

}else
{	System.out.println("inside  setting null");
	ps.setString(8,project_activity_serial_no); // set Value
}

Open in new window




0
 
LVL 47

Expert Comment

by:for_yan
ID: 35026154
You don't have to keep it always null
you can have it:

if(...)stmt.executeUpdate(.....one string)
else
smtm.executeUpdate(another string)

or you can prepare that string upfromt:

String s = "insert into ...."

if(...) s+= "null"
else
s+=  (some value)

and in the end you'll have stmt.executeUpdate(s)
but value of s will be different
 
0
 

Author Comment

by:cofactor
ID: 35026230
but I'm doing a Batch insert .  I'm doing such multiple  inserts in a loop and doing
ps.addBatch();  and at the last I'm doing    ps.executeBatch();

0
 
LVL 47

Expert Comment

by:for_yan
ID: 35026280
Well, at least I suggest to try and to see if it works - after that you can think and compare if it ois really slower, etc. But at least tou maybhave some way which works
0
 

Accepted Solution

by:
cofactor earned 0 total points
ID: 35026905
problem resolved.

reason: silly mistake

code should be

ps.setNull(7, java.sql.Types.INTEGER)  instead of  ps.setNull(8, java.sql.Types.INTEGER)

I'm sorry for wasting your time for such silly mistakes.  I'm accepting this as my answer.
0
 

Author Closing Comment

by:cofactor
ID: 35067807
solved myself
0

Featured Post

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

By the end of 1980s, object oriented programming using languages like C++, Simula69 and ObjectPascal gained momentum. It looked like programmers finally found the perfect language. C++ successfully combined the object oriented principles of Simula w…
In this post we will learn how to make Android Gesture Tutorial and give different functionality whenever a user Touch or Scroll android screen.
The viewer will learn how to implement Singleton Design Pattern in Java.
This video teaches viewers about errors in exception handling.

729 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question