Foreign key error, in my JSP Project

[furious_gamer]

Hi friends,
I am developing an Web Application using JSP/Servlet and MySQL as backend.. I have a main table having 17fields. Out of 17, 4 were foreign key..(I hope u all know foreign key...A field linked from other tables primary key)...
In the first JSP form, user only wants to enter three forein key fields along with other datas and the fourth one left aside...
But it show me an error saying that foreign_key field cannot be "null".

I try to fix this by storing the string "NULL" in a variable(Like String temp="NULL") and in PreparedStatement, i give like this
ps.setString(1,temp);
instead of the previous one like
ps.setNull(1,21);//Here 21 is the equivalent for String

But now it shows a weird error like this

java.sql.SQLException: Duplicate key or integrity constraint violation, message
from server: "Cannot add or update a child row: a foreign key constraint fails
(`raj1/tbl_rmadetails`, CONSTRAINT `fk_raj1tbl_vendortbl` FOREIGN KEY (`VENDOR_I
D`) REFERENCES `tbl_vendor` (`vendor_id`) ON DELETE NO ACTION ON UPDATE NO ACTIO
N)"
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:1977)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1163)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1272)
at com.mysql.jdbc.Connection.execSQL(Connection.java:2236)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java
:1741)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java
:1588 )
at com.rajk.javacode.servlets.RMAEntryModel.addEntry(RMAEntryModel.java:
266)
at com.rajk.javacode.servlets.RMAEntryServlet.doPost(RMAEntryServlet.jav
a:51)

Now dudes, please tell me whats the possible cause for the problem....

Yeah, i knew that foreign key shouldnt left null, but my client doesnt want to enter that particular foreign key field....

And FYI i cant change that foreign key field to normal one as it collapses the project..

Help me friends....

 

[chandru.in]

IMHO, the flaw lies in you DB design and not at UI level. I guess you have not normalized your tables properly. If you give the exact entities involved in the relationships, I can help better.

 

[furious_gamer]

^^
May i know, what i have to do inorder to check where the flaw lies in DB....
And i am not a expert in DB, still a n00b...
I want u to explain a bit( or well if u can ) abt normalization, which will helps me fix this error....

 

[chandru.in]

Normalization is the core of DB design. For more info read here *en.wikipedia.org/wiki/Database_normalization

I'm no expert too. Understanding and practicing normalization is a skill which can be perfected only by experience. Coz strict adherence to normalization can sometimes hit performance badly.

For a quick fix, I can help you out, if you can post the DB fields in the child table you are talking of and the tables to which the 4 FKs refer to.

 

[furious_gamer]

mysql> desc tbl_rmadetails;
+---------------------+-------------+------+-----+------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------------+-------------+------+-----+------------+----------------+
| DATE | date | YES | | 0000-00-00 | |
| RMA_NO | int(11) | NO | PRI | NULL | auto_increment |
| CUSTOMER_ID | varchar(45) | NO | MUL | | |
| ITEM_ID | varchar(45) | NO | MUL | | |
| CUSTOMER_INV_NO | varchar(20) | YES | | NULL | |
| CUSTOMER_INV_DATE | date | YES | | NULL | |
| CUSTOMER_INV_AMOUNT | int(11) | YES | | NULL | |
| SERIAL_NO | varchar(20) | YES | | NULL | |
| FAULT_DESC | varchar(45) | YES | | NULL | |
| REPLACED_SERIAL_NO | varchar(20) | YES | | NULL | |
| STATUS | varchar(45) | NO | | | |
| VENDOR_ID | varchar(45) | NO | MUL | NULL | |
| VENDOR_INV_NO | varchar(20) | YES | | NULL | |
| VENDOR_INV_DATE | date | YES | | NULL | |
| VENDOR_RMA_REF | varchar(45) | YES | | NULL | |
| REMARKS | varchar(45) | YES | | NULL | |
| DELI_DATE | date | YES | | NULL | |
+---------------------+-------------+------+-----+------------+----------------+
17 rows in set (0.01 sec)
mysql> desc tbl_vendor;
+-----------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+--------------+------+-----+---------+-------+
| vendor_id | varchar(45) | NO | PRI | | |
| vendor_name | varchar(45) | NO | | | |
| vendor_password | varchar(45) | NO | | | |
| vendor_address | varchar(200) | YES | | NULL | |
+-----------------+--------------+------+-----+---------+-------+
4 rows in set (0.02 sec)

Oops, sorry for this...the main table "tbl_rmadetails" just have 3 FK fields and one PK..
Sorry for that..Anyway the problem is i dont want to enter the VENDOR_ID FK field..

Help me fix this error...

 

[chandru.in]

I was wondering why it was not allowing null in the first place. Coz you can create a row in child table without a parent in which case the foreign key field will be null.

In your case you have made the vendor_id column NOT NULL. This DDL statement should fix it for you.

alter table tbl_rmadetails modify VENDOR_ID varchar(45);

 

[furious_gamer]

It works man....
Thanks for that dude....

(How i forgot to look at that... this reminds me that "damn, u still a n00b in DB!!!

 

[chandru.in]

DB design is a subtle art very few have really mastered. I'm as much of a n00b as you are.