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 -----
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
smime.p7s