Skip to main content
C
crikos
Inspiring
December 16, 2011
Question

Foreign Key Constraints

  • December 16, 2011
  • 2 replies
  • 1192 views

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

This topic has been closed for replies.

2 replies

D
Dan_Bracuk
Inspiring
December 16, 2011

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.

D
Community Expert
Dave WattsCommunity Expert
Community Expert
December 16, 2011

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

Dave Watts, Eidolon LLC
A
Adam Cameron.
Inspiring
December 17, 2011

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

C
crikosAuthor
Inspiring
December 19, 2011

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

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices