Creating Foreign Key, using relations?

[fasterfester]

I am trying to create 2 tables, with a foreign key relationship.

Table: parent, has parentid and name
Table child has childid, parentid, and name

I am inserting some simple data as well. 2 parents, and 2 children
tied to the parents.

Here is my xml:

<tables>
<table name="parent">
<field ColumnName="parentid" CF_DataType="CF_SQL_INTEGER"
PrimaryKey="true" Increment="true" />
<field ColumnName="parentname" CF_DataType="CF_SQL_VARCHAR"
Length="255" />
<data table="parent">
<row parentname="Dad" />
<row parentname="Mom" />
</data>
</table>
<table name="child">
<field ColumnName="childid" CF_DataType="CF_SQL_INTEGER"
PrimaryKey="true" Increment="true" />
<field ColumnName="childname" CF_DataType="CF_SQL_VARCHAR"
Length="255" />
<field ColumnName="parentid" CF_DataType="CF_SQL_INTEGER">
<relation type="label" table="parent" join-field="parentid"
field="parentid" />
</field>
<data table="child">
<row childname="Son">
<field name="parentid" reltable="parent">
<relfield name="parentid" value="Dad" />
</field>
</row>
<row childname="Daughter">
<field name="parentid" reltable="parent">
<relfield name="parentid" value="Mom" />
</field>
</row>
</data>
</table>
</tables>

Hopefully that doesn't look horrible when it posts.

I am getting a 500 StackOverflowError when I run that code. I have
also tried relation type "list", but had errors as well.

Sorry if this is covered in documentation, but I can't seem to find
it.

Thanks!

[Andrea Campolonghi]

Try ti right fields liek that:

<field name="parentid" reltable="parent" relfield name="parentid" value="Dad" />

Andrea

[Steve Bryant]

fasterfester,

Your problem is with this line:

<field ColumnName="parentid" CF_DataType="CF_SQL_INTEGER">
<relation type="label" table="parent" join-field="parentid" field="parentid" />
</field>

That is creating a relation field that is attempting to use itself as
the join field because both ColumnName and join-field have the same
value. So, DataMgr is recursively trying to determine that information
and getting locked in an infinite loop (eventually resulting in a
Stack Overflow error).

DataMgr should have noticed the problem before it went off to an
infinite loop. I will work on that for the next build.

Let me know if you have any more questions or run into any more trouble,

Steve

[fasterfester]

Thanks for the info, Steve and Andrea, but I am still struggling. I
have changed the fields into every combination that I can think of,
and I either get an error from DataMgr or a Stack Overflow.

Sorry to be a pest, but do you think you could correct the line that
is incorrect in my table definition above. Consequently, this might
be an good example to add to your blog or the documentation. (Creating
a parent-child relationship in the DB)

Thanks, Ken

[Steve Bryant]

Ken,

Sorry, I should have provided more information before. DataMgr doesn't
really have a concept of a foreign key as it is all about what
information you want to save into and get out of your database.

I would probably use the following XML:
<tables>
<table name="parent">

<field ColumnName="parentid" CF_DataType="CF_SQL_INTEGER"

PrimaryKey="true" Increment="true" />
<field ColumnName="parentname" CF_DataType="CF_SQL_VARCHAR" Length="255" />
<data table="parent">
<row parentname="Dad" />
<row parentname="Mom" />
</data>
</table>
<table name="child">
<field ColumnName="childid" CF_DataType="CF_SQL_INTEGER"
PrimaryKey="true" Increment="true" />
<field ColumnName="childname" CF_DataType="CF_SQL_VARCHAR" Length="255" />

<field ColumnName="parentid" CF_DataType="CF_SQL_INTEGER" />
<field ColumnName="parentname">
<relation type="label" table="parent" field="parentname"
join-field="parentid" />
</field>
<data table="child">
<row childname="Son" parentname="Dad" />
<row childname="Daughter" parentname="Mom" />
</data>
</table>
</tables>

Note that this won't actually establish the "parentid" field of the
"child" table as a foreign key, but does create a relation field named
"parentname" that holds the value of the "parentname" field from the
"parent" table where the "parentid" fields of each table match.

That "parentname" relation field is also used in the "data" element
for the "child" table obviating the need to use a relfield (though you
could certainly still use that if you prefer).

Does that make sense?

Steve

[fasterfester]

Yes, that does make sense. Thanks for the info. I was hoping that
DataMgr would handle the creation of a foreign key in this situation,
as I have systems that will be accessing this data outside of
DataMgr. Currently I am really just utilizing the ActiveSchema
functionality as a way to create the tables from xml. No problem, it
should be easy to create the FKs on the fly...

[Steve Bryant]

Ken,

I am planning on adding that feature in the future.

In fact, this got me thinking. I could add an "ftable" attribute to a
field that would indicate it as a foreign key.

For example:
<field ColumnName="parentid" CF_DataType="CF_SQL_INTEGER" ftable="parent" />

If that exists, DataMgr could create the foreign key constraint (if it
doesn't already exist) and also use the foreign key and primary key
information for join fields, if the join fields are not indicated.

Any thoughts?

Steve