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!


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

    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);
    ADD PRIMARY KEY (blah_id);
    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,
INSERT INTO blah (blah_id, account_id) VALUES (2, 1);
INSERT INTO blah (blah_id, account_id) VALUES (3,

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 

Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
View raw message