Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

How to Insert NULL into MYSQL

Posted on 2011-03-03
24
Medium Priority
?
1,051 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:Gurvinder Pal Singh
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 40

Expert Comment

by:Gurvinder Pal Singh
ID: 35025529
0
 
LVL 47

Expert Comment

by:for_yan
ID: 35025536
But if it is a foreign key - can it be NULL? Probaly not
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 40

Expert Comment

by:Gurvinder Pal Singh
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

For beginner Java programmers or at least those new to the Eclipse IDE, the following tutorial will show some (four) ways in which you can import your Java projects to your Eclipse workbench. Introduction While learning Java can be done with…
In this post we will learn how to connect and configure Android Device (Smartphone etc.) with Android Studio. After that we will run a simple Hello World Program.
This tutorial will introduce the viewer to VisualVM for the Java platform application. This video explains an example program and covers the Overview, Monitor, and Heap Dump tabs.
This tutorial explains how to use the VisualVM tool for the Java platform application. This video goes into detail on the Threads, Sampler, and Profiler tabs.
Suggested Courses

604 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