Hi Nefi,
 
I am not an expert on architecture issues, but I think the clue lies in your focussing on users, where you should focus on accounts. Or, in other words, when you define a user, you should not worry about accounts. Define users something like this:
 
CREATE TABLE USERS (USER_ID BIGINT NOT NULL (generated as identy or whatever),
                                     USER_NAME VARCHAR(255),
                                     PRIMARY KEY (USER_ID)
                                    )
 
No reference whatsoever to accounts.
 
Now define your accounts keeping in mind the one to one relationship user vs account, something like this:
 
CREATE TABLE ACCOUNTS (ACCOUNT_ID BIGINT NOT NULL (generated as identy or whatever),
                                             USER_ID BIGINT NOT NULL UNIQUE,
                                            PRIMARY KEY (ACCOUNT_ID),
                                            FOREIGN KEY (USER_ID) REFERENCES USERS
                                            )
 
When searching for a user's account, you search the accounts table with the userid.
When searching for a user you search the users table.
 
I did not test or verify the above statements, so there may be typo's or other stupid mistakes.
 
But is this what you are looking for?

Kind regards,
 
Piet Blok

----- Original Message -----
From: Nefi Percola
To: derby-user@db.apache.org
Sent: Tuesday, June 13, 2006 3:45 PM
Subject: nullable FK constraint

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