I'd have to agree. To me, not using foreign keys is only for times when you
really know what you're doing and have a damn fine reason. I've come into
more than one project with a constraint-free data model and even though the
application made it "impossible" for a violation to happen, there were
mysteriously tons of problems.
The best approach is probably to sit down with your dba and talk about why
they want to do this. Usually, its because they want to archive historical
data periodically. There's almost always an alternative to this approach, if
you're willing to shoulder some of the burden on the development team.
Cris
> -----Original Message-----
> From: Andrus Adamchik [mailto:andru..bjectstyle.org]
> Sent: Friday, March 18, 2005 3:12 PM
> To: cayenne-use..bjectstyle.org
> Subject: Re: Foreign keys
>
> 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:38:20 EST