Of course technically this can be done - you can use Cayenne with or
without DB constraints. First Cayenne doesn't care - it will work the same
way. Second, it will try to do its best to preserve integrity rules
defined as relationships.
This becomes a design philosophy issue (or maybe s/philosophy/religion/).
To me constraints is one last thing that separates universal order from
universal chaos. Turn off constraints, and your database becomes an
overblown text file - any application can turn it into garbage in no time.
> i saw in ur tutorial and tutoriadb file in that also u dont have any
> foreign key relationship for ARTIST, GALLERY and PAINTING tables.
That's because tutorial targets MySQL 3.* that does not support
constraints. There is no deeper wisdom in that example.
Just my $.02 :-)
Andrus
> I'm not a DBA type, but I've been down a similar learning curve.
>
> Yes, it is possible to have relationships maintained only in the app.
> In addition (optionally), the database can impose foreign-key
> constraints. Using both approaches would give you a bit more protection
> against corrupted data, in case there was a subtle bug in the
> application... if the app tried to commit an invalid object graph, the
> database would catch it and cause an exception to be thrown.
>
> I believe it is fairly common practice to maintain relationships only in
> the application, as your DBA is asking you to do. (In fact some
> databases might not support foreign-key constraints.) It makes database
> maintenance tasks somewhat easier. Especially when using Cayenne, which
> will do a good job of validating the object graph before it's committed.
> Your application will have an easier job of handling Cayenne's
> validation errors than
> database-level exceptions, so this approach would probably be easier for
> you as well.
>
> If the application is successful (if it lives a long time and the
> database grows large), you can always choose to go back later and add
> foreign-key constraints to the database for that extra bit of
> protection, without changing your application.
>
>
>
> ----- Original Message -----
> From: "Sami Mohammed" <SMOHAMME..ndependenthealth.com>
> To: <cayenne-use..bjectstyle.org>
> Sent: Friday, March 18, 2005 11:28 AM
> Subject: Foreign keys
>
>
>
> My tables structure are given below:
>
> 1. User_table
> user_key number primary key,
> user_id varchar,
> F&Lname varchar,
> psswd varchar,
> lastLogin date,
> ActiveFlag char(1)
> sec_master_key number referening to
> security_master_table(sec_master_key ) or foreign key
>
> 2.sec_master_Table Table:
> sec_master_key number primarykey,
> description varchar,
> err_role_key number foreign key refering to table err_role_table,
> cw_role key number foreign key refering to table cw_role_table,
> last_chng_date date.
>
> 3.err_role_table
> err_role_key number primary key,
> err_role_id varchar,
> err_role_description,
> last_chng_date date
>
> 4. cw_role_table
> cwr_role_key number primary key,
> cw_role_id varchar,
> errcw
> last_chng_date date.
>
> i have question:
>
> in the above table structure if we remove the foreign keys and maintain
> the relationship using in the application, is it possible. My db analyst
> ask me to maintain in webappliction
>
> i saw in ur tutorial and tutoriadb file in that also u dont have any
> foreign key relationship for ARTIST, GALLERY and PAINTING tables.
> But in the datamap.map file, i saw relationship. was it done manually or
> what. I am using the techonolgy for quite some time, but still i am not
> strong, may be i am asking some silly question. sorry for that.
>
> see below for db-relationship
>
> <db-relationship name="paintingArray" source="ARTIST" target="PAINTING"
> toMany="true">
> <db-attribute-pair source="ARTIST_ID" target="ARTIST_ID"/>
> </db-relationship>
> <db-relationship name="paintingArray" source="GALLERY" target="PAINTING"
> toMany="true">
> <db-attribute-pair source="GALLERY_ID" target="GALLERY_ID"/>
> </db-relationship>
> <db-relationship name="toArtist" source="PAINTING" target="ARTIST"
> toMany="false">
> <db-attribute-pair source="ARTIST_ID" target="ARTIST_ID"/>
> </db-relationship>
> <db-relationship name="toGallery" source="PAINTING" target="GALLERY"
> toMany="false">
> <db-attribute-pair source="GALLERY_ID" target="GALLERY_ID"/>
> </db-relationship>
> <obj-relationship name="paintingArray" source="Artist" target="Painting"
> db-relationship-path="paintingArray"/>
> <obj-relationship name="paintingArray" source="Gallery"
> target="Painting" db-relationship-path="paintingArray"/>
> <obj-relationship name="toArtist" source="Painting" target="Artist"
> db-relationship-path="toArtist"/>
> <obj-relationship name="toGallery" source="Painting" target="Gallery"
> db-relationship-path="toGallery"/>
This archive was generated by hypermail 2.0.0 : Fri Mar 18 2005 - 15:11:33 EST