Re: nullable FK constraint

[Piet Blok]

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