Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1057
  • Last Modified:

How to Insert NULL into MYSQL

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
cofactor
Asked:
cofactor
  • 9
  • 9
  • 3
  • +2
1 Solution
 
Gurvinder Pal SinghCommented:
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
 
for_yanCommented:
But if it is a foreign key - can it be NULL? Probaly not
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
Gurvinder Pal SinghCommented:
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
 
for_yanCommented:
Yes, so maybe it is set NOT NULL - it is worth checking, as the format of inserting NULL seems to have been correct
0
 
objectsCommented:
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
 
Sathish David Kumar NCommented:
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
 
Sathish David Kumar NCommented:
That relationship table colum may not be null so it wont allow you insert null value .
0
 
cofactorAuthor Commented:
>>>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
 
for_yanCommented:
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
 
cofactorAuthor Commented:
>>>Can you try to insert this row not from java, but from SQL prompt?

plenty !  see the records
 project
0
 
cofactorAuthor Commented:
>>>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
 
for_yanCommented:
can you insert the same command from JDBC without PreparedStatement but with null explicitly
among the values ?
0
 
cofactorAuthor Commented:
>>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
 
for_yanCommented:
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
 
cofactorAuthor Commented:
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
 
for_yanCommented:
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
 
for_yanCommented:
Of course I forgot the closing parenthesis in th eendo of the line
0
 
cofactorAuthor Commented:
>>>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
 
for_yanCommented:
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
 
cofactorAuthor Commented:
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
 
for_yanCommented:
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
 
cofactorAuthor Commented:
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
 
cofactorAuthor Commented:
solved myself
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 9
  • 9
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now