db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Nefi Percola <nefip...@yahoo.com>
Subject Re: nullable FK constraint
Date Tue, 13 Jun 2006 17:46:32 GMT
Hi Daniel,

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 <djmorton42@yahoo.com> 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  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 
Mime
View raw message