Creating Relations: Structure Editor vs SQL Code (ADD

Randy Engle (5/14/14 12:24PM)
Jeremy Roussak (5/14/14 9:50PM)
David Adams (5/15/14 9:23AM)
Randy Engle (5/15/14 3:42PM)
Alan Chan (5/15/14 5:19PM)
David Adams (5/15/14 8:19PM)
David Adams (5/16/14 8:30AM)
Keith Culotta (5/16/14 12:36PM)
Julio Carneiro (5/16/14 2:48PM)

Randy Engle (5/14/14 12:24 PM)

CONSTRAINT)Found Major Difference!

<<!&!AAAAAAAAAAAYAAAAAAAAAHuqyL9BU7RPvZs4aFc5Q6XCggAAEAAAAFIilD89cWhKovyrR
KSkSwQBAAAAAA==3D@xc2.us>


FYI: ?For those that might be curious about this, I've found a major
functional difference in drawing relations by hand in the structure
editor
vs using SQL Code and ADD CONSTRAINT

This is v14.1 (Windows)

I'm the midst of a major change to my application, where I've been
changing
all of the primary keys from LongInts to Alphas
(Yes, I've got a good reason for this)
In the process, upgrading to ?4D v14.1

My app has over 200 tables and 190+ relations
I am in the 3rd week of this transition, it's a big job.

As part of making things a little easier, I was hoping to use SQL to
do some
of the job:

1.Removing existing relations (works!)
2.Changing data type from LongInt to Alpha (works!)
3.Creating Primary Keys (works!)
4.Re-establishing relations...works...uhh...er... Oh S**T

This lead me to seeing if I could find out what the actual functional
difference is.

Simple example:

2 Tables
[One_Table]
[One_Table]One_Table_PK_ID (alpha 24)

[Many_Table]
[Many_Table]Many_Table_PK_ID (alpha 24)
[Many_Table]One_Table_PK_ID (alpha 24)

Set the primary key (in structure editor in 4D) for each table
Draw a relation between:
[Many_Table]One_Table_PK_ID -> [One_Table]One_Table_PK_ID

Create some records in each table
Populate the [Many_Table]One_Table_PK_ID with something e.g. "X" (not
the
One Table PK)
Change the [Many_Table]One_Table_PK_ID in a record, either in the
entry form
or programattically.
No problem

Now:

Delete the Relation
Create a relation using SQL
Try changing the value of: ?[Many_Table]One_Table_PK_ID and saving the
record.

No can do!
Error: 1046 Cannot Save Record in [Many Table]
Error: ?1155 No Primary Key Matching Foreign Key

Remove the SQL constraint and put back the relation in the 4D Editor
(Exit and restart)
Try changing data in: [Many_Table]One_Table_PK_ID
No problemo.

Yes, Toto, there is a major difference between these two "similar"
methods.
Many of you may know this already, but for those who don't, here ya'
go.
This is undoubtedly not the only difference, but is a key item, that I
will
need to rethink, among others.

Randy Engle
XC2 Software LLC

color><param>8826F,0000,8219/param>BBesides having a different
appearance, does anybody know if there is a
/color><color><param>00000,0000,DDEE/param>ssignificant difference
between drawing a relation by hand in the structure
editor vs using SQL (ADD CONSTRAINT) to create the relation?
/color><color><param>8826F,0000,8219/param>II'm upgrading to v14 and
things would go a lot faster if there's no
/color>pproblem
color><param>00000,0000,DDEE/param>wwith using ADD CONSTRAINT and SQL
vs drawing relations by hand.

/color>
Jeremy Roussak (5/14/14 9:50 PM)

CONSTRAINT) Found Major Difference!

Fair point. I'd not considered such promiscuity!

Your warning is reasonable, but without looking at some form of SQL
standard, I wonder if in fact the problem is that we've grown used to
4D's laxity and don't like the stricter approach imposed by its
implementation of SQL.

Jeremy

Jeremy Roussak
jbr@...

On 14 May 2014, at 20:48, Randy Engle <4d.list@... wrote:

color><param>00000,0000,DDEE/param>JJeremy,

/color><color><param>8826F,0000,8219/param>IIf not, isn't what the SQL
relationship is insisting on at least arguably
correct, in not allowing the creation of "orphan" records in
[Many_Table]?
/color><color><param>00000,0000,DDEE/param>
Yes, you could say that this is essentially true. ?I agree.

However, in the real world, there do exist situations where a
MANY_TABLE
might have relations with multiple ONE_TABLEs.
(Very polygamous, assuredly!)
In some of those situations, one or more of those relations may not
necessarily be "active", on one may want to inactivate it by removing
the
data in the MANY_TABLE that is establishing the relation.
In this scenario, 4D won't let you do that, if the relation is created
by
SQL.

Randy Engle

/color><color><param>8826F,0000,8219/param>------Original Message-----
From: 4d_tech-bounces@... [mailto:4d_tech-
bounces@... On Behalf Of Jeremy Roussak
Sent: Wednesday, May 14, 2014 12:36 PM

CONSTRAINT) Found Major Difference!

What value are you putting into [ManyTable]One_table_PK_ID? Is there a
corresponding value in [One_Table]One_Table_PK_ID?

If not, isn't what the SQL relationship is insisting on at least
arguably
correct, in not allowing the creation of "orphan" records in
[Many_Table]?

Just a thought.

Jeremy

/color>
David Adams (5/15/14 9:23 AM)

CONSTRAINT) Found Major Difference!

<CAPXPcQs-0ohJoR3SoWiMiGUz1XbSu4Eqtq0gLzNuwFG5jJ-czw@...

color><param>00000,0000,DDEE/param>YYour warning is reasonable, but
without looking at some form of SQL
/color>sstandard,
color><param>00000,0000,DDEE/param>II wonder if in fact the problem is
that we've grown used to 4D's laxity
/color>aand don't
color><param>00000,0000,DDEE/param>llike the stricter approach imposed
by its implementation of SQL.
/color>
No.

If anything, the problem is that 4D doesn't offer enough cardinality
types
and controls on relations. You can have a "many" record linked to a
"one"
record where the one record is absolutely required or option. When I
was a
kid, they called a relationship where the child required the parent an
"identifying" relation. Meaning, without the parent you can't have the
child logically. (Line item without invoice.) You can also have
non-identifying relations where the parent is optional for some reason.
It's called non-identifying because the field is not a required
component
of the (logical) key - you don't need the field to have a value to
identify
("distinguish" really) the row uniquely in its table.

With a 4D relation, is the many-to-one required or not? It's ambiguous
and
4D jumps one way for a drawn line and the other way for a procedurally
set
relation.

Regarding a child that can have one of many different parents - a sort
of
"type of" relationship. Well, yeah, that's not a great fit for the
relational model, IMO. Pity since it's such a dirt-common real world
relationship!
Randy Engle (5/15/14 3:42 PM)

CONSTRAINT) Found Major Difference!

<<!&!AAAAAAAAAAAYAAAAAAAAAHuqyL9BU7RPvZs4aFc5Q6XCgAAAEAAAAAQ0jm+xLZtIm9pDk
6ULZ5YBAAAAAA==3D@xc2.us>


David,

Thanks for adding to this discussion.
It is greatly appreciated!

color><param>00000,0000,DDEE/param>pperhaps there is a
way to define the relation in a way that builds constraints
identically to
/color>hhow
color><param>00000,0000,DDEE/param>44D's structure editor does?
Perhaps you can tweak the system tables?
/color>
This would be awesome.

I've just not figured out how to do it.

Randy Engle
Alan Chan (5/15/14 5:19 PM)

CONSTRAINT) Found

This is common in manufacturing world. A component (child) is part of
BOM
(bill of materials) of many other components (parent) and itself have
its
own BOM that contain other components. In many cases, a component is a
child of its parent and at the same time, a child of its brother (same
generation) or its uncle. Writing MRP/MRPII is fun:-)

Alan Chan

4D iNug Technical <4d_tech@... writes:
color><param>00000,0000,DDEE/param>RRegarding a child that can have
one of many different parents - a sort of
"type of" relationship. Well, yeah, that's not a great fit for the
relational model, IMO. Pity since it's such a dirt-common real world
relationship!
/color>
David Adams (5/15/14 8:19 PM)

CONSTRAINT) Found

<CAPXPcQu683F7zJp1MXj94W=mMK55_sMxGTrtYZi3YPsD=EkzUA@mail.gmail.com>

I'd normally use a recursive relation for a BOM/assembly system since
you
can't predict how many levels down you'll need to go. You need a two
field
table with keys that point back to the main part/source table. The
hardest
part is the code for the interface!
David Adams (5/16/14 8:30 AM)

CONSTRAINT) Found Major Difference!

<CAPXPcQvN1ppSWNqkftc6od9y5eMu-SFwQRhCo1sOWBXfxts-yw@...

After receiving some private email, I had another look at this thread
and
figured I should add a few points. (I'm surprised that no one from 4D
has
chimed in by now.)

First up, yes, the relational model absolutely allows for dependent and
independent child:parent relations. The exact syntax for declaring
relations varies amongst SQL dialects.

When you draw a relation in 4D, a constraint is defined internally.
These
seem to be viewable in the _USER_CONSTRAINTS and _USER_CONS_COLUMNS
system
tables. Tip: When I define relations in the 4D structure editor I give
them
descriptive names which makes the system table rows easier to read.

When you use ALTER TABLE and the foreign_key_definition clause to
establish
a relation (I assume that's what you're doing), perhaps there is a way
to
define the relation in a way that builds constraints identically to how
4D's structure editor does? Perhaps you can tweak the system tables? I
have
no idea - I've never tried any of this using 4D's SQL and the docs
are....sparse.
Keith Culotta (5/16/14 12:36 PM)

CONSTRAINT) Found Major Difference!

color><param>00000,0000,DDEE/param>tthat is, since v11.2 (yes, .2),
the QUERY BY FORMULA command has been
given a completely new role,
which is to perform a relational query on non relational fields.
/color>

I didn?=80&ocirc;t realize this until now. I have not looked at the
command for
a long time, so am I seeing a typo where one does not exist? ¬=A0Any
way
I try to change it, I do not understand its significance.

Original:
QUERY BY FORMULA([invoice];(([invoice_line]amount==E2&Auml;&uacute;4?=80&ugrave;))
&
([invoice_line]invoice_id=[invoice]id)))

If typo fix =
QUERY BY FORMULA([invoice];(([invoice_line]amount==E2&Auml;&uacute;4?=80&ugrave;))
&
([invoice_line]invoice_id=[invoice]id))
or typo fix=
QUERY BY FORMULA([invoice];(([invoice_line]amount==E2&Auml;&uacute;4?=80&ugrave;)
&
([invoice_line]invoice_id=[invoice]id)))

Why is it different from:
QUERY BY FORMULA([invoice];([invoice_line]amount==E2&Auml;&uacute;4?=80&ugrave;)
&
([invoice_line]invoice_id=[invoice]id))

Keith
¬=A0¬=A0¬=A0¬=A0¬=A0¬=A0¬=A0¬=A0¬=A0Computer
Dimensions, Inc.

On May 16, 2014, at 10:23 AM, Keisuke Miyako <Keisuke.Miyako@...
wrote:

color><param>00000,0000,DDEE/param>OOK then, try

QUERY BY FORMULA([invoice];\
(([invoice_line]amount="4?=80&ugrave;)) & \
([invoice_line]invoice_id=[invoice]id)))

it seem the formula portion and join portion should be explicitly
separated.

On 2014/05/16 12:29, "Keisuke Miyako" <Keisuke.Miyako@... wrote:

/color><color><param>8826F,0000,8219/param>QQUERY BY
FORMULA([invoice];([invoice_line]amount="4?=80&ugrave;) & \
([invoice_line]invoice_id=[invoice]id))
/color><color><param>00000,0000,DDEE/param>
/color>
Julio Carneiro (5/16/14 2:48 PM)

CONSTRAINT) Found Major Difference!

huh! Are you saying QBF can now replace Mr. Adams' Free_Join?
really?

julio

On May 16, 2014, at 12:23 PM, Keisuke Miyako <Keisuke.Miyako@...
wrote:

color><param>00000,0000,DDEE/param>OOK then, try

QUERY BY FORMULA([invoice];\
(([invoice_line]amount="4?=80&ugrave;)) & \
([invoice_line]invoice_id=[invoice]id)))

it seem the formula portion and join portion should be explicitly
separated.

On 2014/05/16 12:29, "Keisuke Miyako" <Keisuke.Miyako@... wrote:

/color><color><param>8826F,0000,8219/param>QQUERY BY
FORMULA([invoice];([invoice_line]amount="4?=80&ugrave;) & \
([invoice_line]invoice_id=[invoice]id))
/color><color><param>00000,0000,DDEE/param>
/color>
Reply to this message

Summary created 5/16/14 at 1:14PM by Intellex Corporation

Comments welcome at: feedback@intellexcorp.com