Enhancement suggestion: foreignKey linking to column with isUnique constraint

[Blama]

Hi Isomorphic,

it would be great if you could allow linking between a column in a child table and a unique - but not primaryKey - column in DataSources as well.
This is allowed in Oracle as well and I'm using the feature there.

Currently, in order to mimic the feature, I have to create a copy of the .ds.xml for the parent table and change the PK in the copy to the unique field.
I then reference the copy and not the original in the child's foreignKey definition.

This is pretty minor as there is a workaround and I'm using it only twice so far.

Best regards
Blama

[Isomorphic]

DataSources allow multiple foreignKeys, so why are you creating a separate .ds.xml for this? Is there some SQL syntax that we're using that actually requires the target field of an FK to be a PK? It doesn't immediately appear that there is a need for an enhancement for this use case to work.

[Blama]

Hi Isomorphic,

see this .ds.xml excerpt (my STATUS table):

        <field [B]primaryKey="true"[/B] hidden="true" name="[B]ID[/B]" type="sequence" />
        <field hidden="true" name="TENANT_ID" type="integer" canEdit="false" />
        <field foreignKey="V_USER_CREATED_BY.ID" relatedTableAlias="USER_CREATED_BY" name="CREATED_BY" type="creator">
            <title><fmt:message key="createdBy" /></title>
        </field>
        <field name="CREATED_AT" type="creatorTimestamp">
            <title><fmt:message key="createdAt" /></title>
        </field>
        <field foreignKey="V_USER_MODIFIED_BY.ID" relatedTableAlias="USER_MODIFIED_BY" name="MODIFIED_BY" type="modifier">
            <title><fmt:message key="modifiedBy" /></title>
        </field>
        <field name="MODIFIED_AT" type="modifierTimestamp">
            <title><fmt:message key="modifiedAt" /></title>
        </field>
        <field name="[B]SHORTNAME[/B]" length="30" type="text" escapeHTML="true" required="true" canSave="false">
            <title><fmt:message key="shortname" /></title>
            <validators>
                <validator [B]type="isUnique"[/B]>
                    <errorMessage><fmt:message key="validatorShortNameInUse" /></errorMessage>
                </validator>
            </validators>
        </field>

This table is present in every instance of my application.
It always has 10 rows which have all the same 10 different possible shortnames in every instance. The ID is not always the same, but every time different.
All internal FK relationships referencing the table go to the ID.

Now I have the case that I want to reference different status from a CSV file. I obviously don't use the IDs, that are meaningless to a end user, but the shortname.
In order to do so I create a copy of this file with a different Datasource-ID, but the same tablename. In the copy I don't have the ID column at all and have primaryKey="true" for the shortname.

Now I can link to this "readonly" (because of the missing notion of the sequence, that would obviously be needed for an DB-INSERT) table.

A foreign key to this table is now:

<field name="STATUS_SHORTNAME" [B]foreignKey="STATUSCOPY.SHORTNAME"[/B] ...

where a foreign key to the base table is

<field name="STATUS_ID" [B]foreignKey="STATUS.ID"[/B] ...

With the enhancement, this would be possible:

<field name="STATUS_SHORTNAME" [B]foreignKey="STATUS.SHORTNAME"[/B] ...

because the field shortname in table status is unique.
This would save me from having to copy and modify the DataSouce.

Of course another relational design with the shortname as the PK in the database would have saved me from having this enhancement suggestion, but then many many foreignKeys in my application would link text fields - something I don't like and you don't generally recommend.

So the suggestion is to allow FKs to link to unique columns, like the database already does.

Best regards
Blama

[Isomorphic]

So again, SmartGWT does not check whether the target column is actually a PK - we allow the FK declaration anyway.

You've introduced a bunch of application-specific concerns here, but you don't seem to have addressed what we said: there doesn't seem to be a need for an enhancement for allowing an FK to point to a non-PK column. So far as we know, that already works.

You might try this out with a simpler case, outside of your application, if you need to see it working. Then you can revisit your application and see if you are overcompicating things, or if in fact the enhancement you need is unrelated to what you're requesting here.

[Blama]

Hi Isomorphic,

sorry, I misunderstood your post #2. I tried it before in my application, but retesting with Animals/SupplyItem shows that the linking is working as you say.
I'll then get rid of my unneeded extra datasources.

Thank you & Sorry for bothering
Blama