Solved

How to Insert NULL into MYSQL

Posted on 2011-03-03
24
1,014 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
  • 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 40

Expert Comment

by:gurvinder372
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
 
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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Suggested Solutions

INTRODUCTION Working with files is a moderately common task in Java.  For most projects hard coding the file names, using parameters in configuration files, or using command-line arguments is sufficient.   However, when your application has vi…
Go is an acronym of golang, is a programming language developed Google in 2007. Go is a new language that is mostly in the C family, with significant input from Pascal/Modula/Oberon family. Hence Go arisen as low-level language with fast compilation…
This tutorial covers a practical example of lazy loading technique and early loading technique in a Singleton Design Pattern.
This theoretical tutorial explains exceptions, reasons for exceptions, different categories of exception and exception hierarchy.

757 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now