Thanks for the reply. You're right about User, I actually named it User_Master. Anyway, I think you're right about the null since I just tried a simple example and I can insert it. I went back and did a hardcore trace on the hibernate inserts and saw that its not null thats being set, but an actual account id that, for some reason, is not in the account table. I think this is whats causing the FK constraint. The java code representing User actually creates an account using reflection--i.e. if an account does exist for the user, it will retrieve that account via reflection... the problem is the account is in-memory and not in the database until the call to saveUser().... I guess I need to do more refactoring... Thanks!
-nefi
Daniel Morton <[EMAIL PROTECTED]> wrote:
Hi Nefi:
I'm reasonably certain that by defenition, a foreign
key field can be NULL, unless you explicitly define it
as not null, which you did not, so that should not be
your problem... However, I just did a quick test, and
I was not even allowed to create a table called
'user'... I belive this is a Derby/SQL reserved
word... Perhaps you should try creating your tables
with different names, and see what happens.
For Example, I ran the following script, which is more
or less similar to what you are doing, and it worked
fine:
CREATE TABLE blah (
blah_id BIGINT not null,
account_id BIGINT
);
CREATE TABLE account (
account_id BIGINT not null,
account_number BIGINT
);
ALTER TABLE account
ADD PRIMARY KEY (account_id);
ALTER TABLE blah
ADD PRIMARY KEY (blah_id);
ALTER TABLE blah
ADD CONSTRAINT blah_id_fk
FOREIGN KEY (account_id)
REFERENCES account (account_id);
INSERT INTO account (account_id, account_number)
VALUES (1, 999999);
INSERT INTO blah (blah_id, account_id) VALUES (1,
null);
INSERT INTO blah (blah_id, account_id) VALUES (2, 1);
INSERT INTO blah (blah_id, account_id) VALUES (3,
null);
Daniel Morton
--- Nefi Percola <[EMAIL PROTECTED]> wrote:
> Hi,
>
> This maybe more of a general DB question rather than
> a derby question but related. I have a one-to-one
> relationship between user and account. Its really
> unidirectional: User knows about account but not
> vice-versa. I used Hibernate to generate a schema
> on my derby database that uses a many-to-one
> relationship w/FK constraint--making it one-to-one.
>
>
> The problem I'm encountering is that in my business
> domain, there are certain points in the workflow
> where a user can exist but has no account.
> Therefore I should be able to save this user into
> the database with a null account. However, based on
> the schema generated, derby throws a "FK constraint"
> when I try to put a null value into the account
> column of user. Is there a way to allow nullable
> values for foreign keys like this account? This is
> the schema for the user table that Hibernate
> generated for me:
>
> ###################
> Create table user (
> user_id BIGINT not null,
> account_id BIGINT)
>
> alter table user add primary key (user_id);
>
> alter table user
> add constraint FKC65A7C975E38143A
> foreign key (account_id)
> references account (account_id)
> on delete no action
> on update no action;
> .....
> ###########################
>
> any help would be appreciated. Thanks!
>
> -nefi
>
> __________________________________________________
> Do You Yahoo!?
> Tired of spam? Yahoo! Mail has the best spam
> protection around
> http://mail.yahoo.com
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
