db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Craig L Russell <Craig.Russ...@Sun.COM>
Subject Re: nullable FK constraint
Date Tue, 13 Jun 2006 18:13:02 GMT
Hi,

On Jun 13, 2006, at 10:54 AM, Nefi Percola wrote:

> 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!

This feature is called "deferred constraint checking". It's usually  
defined on a constraint, as in Oracle:
ALTER TABLE chicken ADD CONSTRAINT chickenREFegg
     FOREIGN KEY (eID) REFERENCES egg(eID)
     INITIALLY DEFERRED DEFERRABLE;
ALTER TABLE egg ADD CONSTRAINT eggREFchicken
     FOREIGN KEY (cID) REFERENCES chicken(cID)
     INITIALLY DEFERRED DEFERRABLE;

http://www-db.stanford.edu/~ullman/fcdb/oracle/or-triggers.html

Please see http://issues.apache.org/jira/browse/DERBY-532 for the  
status of the Derby feature. I hope that the Derby issue is accurate...

Craig
>
> -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
>
>

Craig Russell
Architect, Sun Java Enterprise System http://java.sun.com/products/jdo
408 276-5638 mailto:Craig.Russell@sun.com
P.S. A good JDO? O, Gasp!


Mime
View raw message