db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Piet Blok" <pbh...@wanadoo.nl>
Subject Re: nullable FK constraint
Date Tue, 13 Jun 2006 17:12:36 GMT
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 

Mime
View raw message