Foreign Key Constraints

[crikos]

Hi everybody,

does anybody have suggestions, for creating foreign keys in a MySQL DB using Coldfusion9. The traditional way is to define the constraints in MySQL. Beside that I have to define those relationships also in Coldfusion.

Should I define the constraints on both sides, or just in Coldfusion ? As I tried out it is working, if I do the definitions only in CF9.

Thanks for answering.

Regards

[Dave Watts]

I'm not sure what you mean by "define those relationships also in ColdFusion". Do you mean within ORM? In any case, though, if you need constraints they should be defined within your relational database first and foremost.

Dave Watts, CTO, Fig Leaf Software

[Adam Cameron.]

I'm not sure what you mean by "define those relationships also in ColdFusion". Do you mean within ORM? In any case, though, if you need constraints they should be defined within your relational database first and foremost.

I'm presuming the OP does indeed mean in the context of defining a mapping between a CF object and DB storage via Hibernate.

One can use the CF ORM mappings in two different ways:

1) start with nothing in the DB, and the ORM mapping also creates the DB requirements (tables, keys, constraints etc).  In which case the mappings in CF do reflect the constraints that will end up in the DB too.  That's what those settings are for.

2) mapping already existing DB resources to a CF component.  In this situation I don't think CF will create new constraints if the tables already exist.  Although there's one very easy way to test that...

Either way, the constraints ought to be present in the DB, yes.  If CF's ORM mapping doesn't do it itself, then it ought to be done by hand.

--

Adam

[crikos]

Hi Adam,

thanks for your answer.

I did some tests on various tables - with and without the constraints in the DB. It is working in either way, as I understand ORM and Hibernate in CF, the integrity of the tables is maintained by CF (ORM/Hib). What are the advantages of doing the definition twice ? If I am defining all constraints just in CF, some possible steps in the future (e.g. migration to another DB System) could be much easier, because the model is maintained one layer above the DB. Or are there other restrictions ?

Regards

[Dan_Bracuk]

You should always acknowledge the possibility that the db will be accessed without using ColdFusion.  Once you do so, do you think foreign key constraints will be useful at the database level?

BTW, like Dave Watts, I don't know what you mean when you say you define relationships in ColdFusion.