[Dbix-class] complex relationships I

[Benjamin Hitz]

Oh, I forgot, they are in the DB, but not in the tables.sql file.
That is how our DBA likes them - keeps the foreign keys and  
constraints in separate files.

ALTER TABLE CV_TERM
ADD (CONSTRAINT CV_TERM_PK PRIMARY KEY
   (CV_TERM_NO)

ALTER TABLE CVTERM_RELATIONSHIP ADD (CONSTRAINT
CVTREL_CHILD_CVTERM_FK FOREIGN KEY
   (CHILD_CV_TERM_NO) REFERENCES CV_TERM
   (CV_TERM_NO) ON DELETE CASCADE)
/

ALTER TABLE CVTERM_RELATIONSHIP ADD (CONSTRAINT
CVTREL_PARENT_CVTERM_FK FOREIGN KEY
   (PARENT_CV_TERM_NO) REFERENCES CV_TERM
   (CV_TERM_NO) ON DELETE CASCADE)
/

I will add the constraints for the other tables in another email.
Sorry, I just forgot to put them in the email

Ben


On Sep 20, 2007, at 3:23 PM, Mark Lawrence wrote:

> On Thu Sep 20, 2007 at 11:32:20AM -0700, Benjamin Hitz wrote:
>
> Hi Benjamin,
>
>> Hi, we are in the process of replacing an old, hand-rolled API (and
>> bags-o-sql) with DBIc.  Our Oracle database is pretty large (~110
>> tables) with some complicated relationships.  We have used
>
> ...
>
>> So CVTERM_RELATIONSHIP has two foreign keys to the CV_TERM table.
>
> This is slightly off topic, but you mention quite a few relationships
> between tables, however according to the SQL statements you gave none
> of the foreign keys have been defined.
>
> I would like to suggest that you make relationships between the tables
> explicit, especially given the apparent complexity. It would certainly
> be easier to understand what's going on when inspecting the schema  
> (and
> for the people you ask questions of :-) not to mention the additional
> benefit of the database forcing some integrity on your application.
>
> If what you posted was simply some pseudo SQL then please ignore my
> ramblings.
>
> Regards,
> Mark.
> -- 
> Mark Lawrence
>
> _______________________________________________
> List: http://lists.rawmode.org/cgi-bin/mailman/listinfo/dbix-class
> IRC: irc.perl.org#dbix-class
> SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
> Searchable Archive: http://www.grokbase.com/group/dbix- 
> class at lists.rawmode.org

--
Ben Hitz
Senior Scientific Programmer ** Saccharomyces Genome Database ** GO  
Consortium
Stanford University ** hitz at genome.stanford.edu