Thanks for the reply. Actually I followed Daniel's post and he was right about the FK constraint. My real problem is that I'm trying to persist a user that already has an account id into the database before the account record is inserted. I think this is whats causing the FK constraint.
Just curious, is there a way to allow this situation to happen in Derby (or any other DB in general)... allow user to be store with an account id, and then store the account (with the same account id) into the account table? I would probably need to take the constraint out of the FK but not sure how or if this even doable. Thanks!
-nefi
Piet Blok <[EMAIL PROTECTED]> wrote:
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 PercolaSent: Tuesday, June 13, 2006 3:45 PMSubject: nullable FK constraintHi,
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
