db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Nefi Percola <nefip...@yahoo.com>
Subject Re: nullable FK constraint
Date Tue, 13 Jun 2006 17:54:47 GMT
Hi Piet,

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 <pbhome@wanadoo.nl> 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 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 


 __________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 
Mime
View raw message