You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by Nefi Percola <ne...@yahoo.com> on 2006/06/13 15:45:55 UTC
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
Posted by Craig L Russell <Cr...@Sun.COM>.
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 <pb...@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!
Re: nullable FK constraint
Posted by Nefi Percola <ne...@yahoo.com>.
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 <pb...@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
Re: nullable FK constraint
Posted by Piet Blok <pb...@wanadoo.nl>.
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
Re: nullable FK constraint
Posted by Nefi Percola <ne...@yahoo.com>.
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!
-nefi
Daniel Morton <dj...@yahoo.com> wrote: 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, 999999);
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 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
Posted by Daniel Morton <dj...@yahoo.com>.
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, 999999);
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 <ne...@yahoo.com> 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