Derby foreign key constraint issue

Question

Hi I am stuck with a foreign key constraint in my derby embedded table. I have three tables namely rooms, packages and reservation.

Room Table--- RoomId | RoomName| Rent | Description

Packages Table --- pkgId | Name | Rate | Details

Reservation Table--- Id | GuestID | pkgId | RoomId | ChkIn | ChkOut | Date |

So the three foreign keys in Reservation tables are GuestID, pkgId and roomId.

All I wanted is the foreign keys to accept some values that is not in their respective tables. For example I want to make pkgId in reservation table to be 0 if a guest is not taking packages. But some reason I am getting error " UPDATE on table 'RESERVATION' caused a violation of foreign key constraint .. for key (0)". Is that because the 0 is not a value in Packages table ? Strangely enough it was not an issue with my earlier application. Did derby changed something with new jar files ? Or how can I solve this issue ? I want both RoomId and pkgId accept 0 in reservation table at some point of time while keeping the foreign key constraint. Can anyone help me ? Thanks in advance!

Answer 1

in SQL, 0 is a valid value just like any other. therefor if you set 0 as an FK column, it will look for a row with 0 on the related table.

If you want to indicate that there is no related row on the other table, you should use null as the ID