2

I have two tables : 

CREATE TABLE possible_noise 
    (id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT, text VARCHAR(50) NOT NULL, 
    PRIMARY KEY(id));

and 

CREATE TABLE diagnostic
    (id BIGINT UNSIGNED NOT NULL, 
    noise SMALLINT UNSIGNED DEFAULT NULL, 
    friction ....., 
    FOREIGN KEY (noise) 
    REFERENCES possible_noise(id), 
    FOREIGN KEY(friction) ...... );

First table is table with possible values for noise test, where "text" is some text value such as "noise is ok" or "too loud" or something like that. Second table is table of product with id of product and with x columns where all columns are for one type of test. If test is not complete there is NULL

Here is my problem :

When I need use something like this ...

PreparedStatement prepState = conn.prepareStatement("UPDATE diagnostic SET noise=?, diagnostic_result=?, software_version=?, .... WHERE id=?;");

...and need put NULL

prepState.setInt(2, java.sql.Types.INTEGER)

it's throwing the error:

Cannot add or update a child row: a foreign key constraint fails (eps_remanufacturing_system.diagnostic, CONSTRAINT diagnostic_ibfk_3 FOREIGN KEY (noise) REFERENCES possible_noise (id))

My question :

Is there possibility how use DEFAULT from sql (http://dev.mysql.com/doc/refman/5.0/en/data-type-defaults.html) in preparedStatement?

jnthnjns
  • 8,962
  • 4
  • 42
  • 65
BrooksWasHere
  • 67
  • 3
  • 10
  • The use of a placeholder implies you will pass a simple value. You cannot pass a keyword, function call or other expression dynamically to a prepared statement, you will have to prepare a different statement with an explicit `noise = DEFAULT`. This is true of prepared statements in every RDBMS I've ever worked with. – DaveRandom Apr 12 '13 at 17:41

1 Answers1

2

You have to replace the placeholder by DEFAULT:

...
SET noise=DEFAULT
...
jlareau
  • 2,860
  • 1
  • 17
  • 11