Hi Jeroen,
Please find below the legacy table schema definition:
CREATE TABLE om_site
(
site_id character varying(80) NOT NULL,
org_id character varying(18) NOT NULL,
site_name character varying(45),
site_desc character varying(60),
site_type character varying(45) NOT NULL, -- Type of the site. Can be CAMPUS
or BUILDING
address_id character varying(10),
location_id character varying(80),
change_by character varying(60) NOT NULL DEFAULT 'System/Admin'::character
varying,
change_dt timestamp without time zone NOT NULL,
CONSTRAINT om_site_pk PRIMARY KEY (site_id, org_id),
CONSTRAINT om_site_address_id FOREIGN KEY (address_id, org_id)
REFERENCES om_address (address_id, org_id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT om_site_factility_org_fk FOREIGN KEY (location_id, org_id)
REFERENCES om_geo_location (location_id, org_id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
)
I have to get this schema in synch with JDO entity mapping.
[Jeroen]: @Column defines the column into which a field is persisted. I can't
see why you want to use the same column for three fields
[Varma]: Because org_id column at the database side is being used in three
constraints (om_site_pk, om_site_address_id, om_site_factility_org_fk)
[Jeroen]: If you don't want to store the address_id on OmSite you can use the
@Persistent(mappedBy="<OmSite field on OmAddress>")
[Varma]: OmSite and OmAddress are related as ManyToOne and bidirectional, so in
OmAddress I have Set<OmSite> sites with annotation @Persistent
(mappedby="omAddress"). And I want to store address_id in om_site table only as
a foreign key. Same case is with the other FK field
Note: The DDL is legacy one, so it could not be changed.
This is a valid scenario at RDBMS side
This kind of mapping works with JPA where in same column can be used as for the
fields above. I tested its working fine(JPA/Hibernate out of ISIS environment)
And also I tried to resolve this issue by adding a column unique
rowsequence_id(not PK) in om_address table, and at om_site foreign key is
changed to refer to that unique rowsequence_id which is not PK. But JDO did not
allow this as FK should refer to PK field only.
This is really annoying as this is also valid at RDBMS side but not at JPA/JDO
side.
BR
Ranganath Varma
BR
Ranganath Varma
-----Original Message-----
From: Jeroen van der Wal [mailto:[email protected]]
Sent: Tuesday, January 07, 2014 4:48 PM
To: users
Subject: Re: ISIS and JDO: Multiple fields(FK's and PK) using same column
throws not-null constraint for PK
@Column defines the column into which a field is persisted. I can't see why you
want to use the same column for three fields. If you don't want to store the
address_id on OmSite you can use the @Persistent(mappedBy="<OmSite field on
OmAddress>") annotation [1].
HTH
[1] http://www.datanucleus.org/products/accessplatform/jdo/orm/one_to_one.html
On Tue, Jan 7, 2014 at 11:54 AM, <[email protected]> wrote:
> Consider this following scenario:
>
> public class OmSite implements java.io.Serializable {
>
> //OmSitePK.class is also created as per the specifications //for these
> two fields. No issue with that @Persistent(primaryKey = "true",
> dependent = "false") @Column(allowsNull = "false", name = "site_id")
> private String siteId;
>
> @Persistent(primaryKey = "true", dependent = "false")
> @Column(allowsNull = "false", name = "org_id") private String orgId;
>
> @Persistent(columns = {@Column(name="address_id", target =
> "address_id"), @Column(name="org_id", target = "org_id")}) @Optional
> private OmAddress omAddress;
>
> @Persistent(columns = { @Column(name = "location_id"), @Column(name =
> "org_id")}) @Optional private OmGeoLocation omGeoLocation;
> //Corresponding PK classes also created as per the specifications.
>
> //getters and setters
>
> }
>
>
> Here I have 1 composite PK(site_id, org_id), 2 composite FK's
> OmGeoLocation(location_id,org_id), OmAddress(address_id, org_id).
> As you can see same column org_id is being used/referred at 3 places
>
> With this mapping, when OmSite is constructed with its PK fields(orgId,
> siteId) and other mandatory fields. But not FK fields(omGeoLocation,
> omAddress), I have got not-null constraint error for one of the fields in
> compiste PK that is org_id.
>
> And when I construct OmSite by setting FKS (omGeoLocation, omAddress)also
> with org_id being same accorss PK and FK's, org_id value is being sent to
> Database.
>
>
> I suspect that org_id column is set when all the fields are set which use
> org_id column.
> This is not valid behaviour. Because OmGeoLocation and OmAddress are optional.
>
> Please help on this issue
>
> Thanks
> Ranganath
>
> BR
> Ranganath Varma
>
>
> The information contained in this electronic message and any attachments to
> this message are intended for the exclusive use of the addressee(s) and may
> contain proprietary, confidential or privileged information. If you are not
> the intended recipient, you should not disseminate, distribute or copy this
> e-mail. Please notify the sender immediately and destroy all copies of this
> message and any attachments.
>
> WARNING: Computer viruses can be transmitted via email. The recipient should
> check this email and any attachments for the presence of viruses. The company
> accepts no liability for any damage caused by any virus transmitted by this
> email.
>
> www.wipro.com
The information contained in this electronic message and any attachments to
this message are intended for the exclusive use of the addressee(s) and may
contain proprietary, confidential or privileged information. If you are not the
intended recipient, you should not disseminate, distribute or copy this e-mail.
Please notify the sender immediately and destroy all copies of this message and
any attachments.
WARNING: Computer viruses can be transmitted via email. The recipient should
check this email and any attachments for the presence of viruses. The company
accepts no liability for any damage caused by any virus transmitted by this
email.
www.wipro.com
