Re: nullable FK constraint
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.htmlPlease 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-nefiPiet 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 PercolaTo: [email protected]: 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 Craig Russell Architect, Sun Java Enterprise System http://java.sun.com/products/jdo 408 276-5638 mailto:[EMAIL PROTECTED] P.S. A good JDO? O, Gasp! smime.p7s Description: S/MIME cryptographic signature
Re: nullable FK constraint
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! -nefiPiet 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 Percola To: [email protected]: Tuesday, June 13, 2006 3:45 PM Subject: nullable FK constraint Hi,This maybe more of a general DB question rather thana derby question but related. I have a one-to-one relationship betweenuser and account. Its really unidirectional: User knows about accountbut not vice-versa. I used Hibernate to generate a schema on my derbydatabase that uses a many-to-one relationship w/FK constraint--making itone-to-one. The problem I'm encountering is that in my businessdomain, there are certain points in the workflow where a user can exist buthas no account. Therefore I should be able to save this user into thedatabase 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 accountcolumn of user. Is there a way to allow nullable values for foreign keyslike this account? This is the schema for the user table that Hibernategenerated for me:###Create table user( user_id BIGINT not null, account_idBIGINT)alter table user add primary key (user_id);alter tableuser add constraint FKC65A7C975E38143A foreignkey (account_id) references account(account_id) on delete no action on update no action;.###any help would beappreciated. Thanks!-nefi __Do YouYahoo!?Tired of spam? Yahoo! Mail has the best spam protection aroundhttp://mail.yahoo.com __Do You Yahoo!?Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
Re: nullable FK constraint
Hi Daniel,Thanks for the reply. You're right about User, I actually named it User_Master. Anyway, I think you're right about the null since I just tried a simple example and I can insert it. I went back and did a hardcore trace on the hibernate inserts and saw that its not null thats being set, but an actual account id that, for some reason, is not in the account table. I think this is whats causing the FK constraint. The java code representing User actually creates an account using reflection--i.e. if an account does exist for the user, it will retrieve that account via reflection... the problem is the account is in-memory and not in the database until the call to saveUser() I guess I need to do more refactoring... Thanks!-nefiDaniel Morton <[EMAIL PROTECTED]> wrote: Hi Nefi:I'm reasonably certain that by defenition, a foreignkey field can be NULL, unless you explicitly define itas not null, which you did not, so that should not beyour problem... However, I just did a quick test, andI was not even allowed to create a table called'user'... I belive this is a Derby/SQL reservedword... Perhaps you should try creating your tableswith different names, and see what happens.For Example, I ran the following script, which is moreor less similar to what you are doing, and it workedfine:CREATE TABLE blah (blah_id BIGINT not null,account_id BIGINT);CREATE TABLE account (account_id BIGINT not null,account_number BIGINT);ALTER TABLE account ADD PRIMARY KEY (account_id);ALTER TABLE blah ADD PRIMARY KEY (blah_id);ALTER TABLE blah ADD CONSTRAINT blah_id_fk FOREIGN KEY (account_id) REFERENCES account (account_id);INSERT INTO account (account_id, account_number) VALUES (1, 99);INSERT INTO blah (blah_id, account_id) VALUES (1,null);INSERT INTO blah (blah_id, account_id) VALUES (2, 1);INSERT INTO blah (blah_id, account_id) VALUES (3,null);Daniel Morton--- Nefi Percola <[EMAIL PROTECTED]> wrote:> 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 __Do You Yahoo!?Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
Re: nullable FK constraint
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: [email protected] 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
Re: nullable FK constraint
Hi Nefi: I'm reasonably certain that by defenition, a foreign key field can be NULL, unless you explicitly define it as not null, which you did not, so that should not be your problem... However, I just did a quick test, and I was not even allowed to create a table called 'user'... I belive this is a Derby/SQL reserved word... Perhaps you should try creating your tables with different names, and see what happens. For Example, I ran the following script, which is more or less similar to what you are doing, and it worked fine: CREATE TABLE blah ( blah_id BIGINT not null, account_id BIGINT ); CREATE TABLE account ( account_id BIGINT not null, account_number BIGINT ); ALTER TABLE account ADD PRIMARY KEY (account_id); ALTER TABLE blah ADD PRIMARY KEY (blah_id); ALTER TABLE blah ADD CONSTRAINT blah_id_fk FOREIGN KEY (account_id) REFERENCES account (account_id); INSERT INTO account (account_id, account_number) VALUES (1, 99); INSERT INTO blah (blah_id, account_id) VALUES (1, null); INSERT INTO blah (blah_id, account_id) VALUES (2, 1); INSERT INTO blah (blah_id, account_id) VALUES (3, null); Daniel Morton --- Nefi Percola <[EMAIL PROTECTED]> wrote: > 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
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
