[PonyORM-list] Inquire about Foreign Key

[Alexander Kozlovsky]

Hi Agatha!

Do I understand correctly that you have pre-existing database and want to
define PonyORM models to work with it?

At the first sight, the tables looks a bit strange. With a good table
design, the primary key should consist of a minimum set of columns, and the
rest columns should functionally depend on the entire primary key. In your
`branches` table, `id` is already unique, so primary key should be `id` and
not a combination of `id` and `client`.

PonyORM assumes that the tables are properly normalized, so it is not
possible to define exactly these tables with Pony, but is is possible to
define models which are "similar enough", so you can do SELECTs and UPDATEs
without any issues. As the database is already exists, you will not call
"CREATE TABLE" commands anyway, so differences in the exact definition of
foreign keys are not important.

You can try to define models in the following way:

    class Branch(db.Entity):
        _table_ = "branches"
        id = PrimaryKey(int)
        client = Required(str)
        parent = Optional("Branch", column="pid")
        config_items = Set("ConfigItem")

    class ConfigItem(db.Entity):
        _table_ = 'branches_config'
        id = PrimaryKey(int, auto=True)
        branch = Required("Branch", column="bid")
        client = Required(str)

In these models, `ConfigItem` entity assumes that the foreign key to
`Branch` is just `id`. It should work because when constructing SQL query
`id` alone is sufficient to find unique instance of a `Branch` object. But
if you create a new `ConfigItem` object, you need to always specify correct
`client` attribute value which should be the same as in the linked Branch
object, otherwise the database will throw an error about the violation of a
foreign key constraint. You can use the following helper functions when
creating a new objects:

    def create_branch(id, client, parent=None):
        return Branch(id=id, client=client, parent=parent)

    def create_config_item(branch):
        return ConfigItem(branch=branch, client=branch.client)

Actually, the table `branches_config` looks useless without any additional
columns, as it does not contains any additional information comparing to
the `branches` table. Anyway, it should works as expected, I think.

By the way, the main discussion is moved from this mailing list to a
telegram channel: https://telegram.me/ponyorm. But this mailing list is
still functioning too

Hope I answered your question

Best regards,
Alexander









On Thu, Oct 11, 2018 at 10:50 AM Agatha Ng <agathang93 at gmail.com> wrote:

> Hello,
>
> I ran into some problem when building the entity relationship with Pony
> ORM. Following are two tables in the database.
>
> TABLE `branches` (
>>   `id` int(11) NOT NULL,
>>   `client` varchar(64) NOT NULL,
>>   `pid` int(11) DEFAULT NULL,
>>   PRIMARY KEY (`id`,`client`),
>>   KEY `branches_ibfk_1` (`pid`),
>>   CONSTRAINT `branches_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `branches`
>> (`id`)
>> )
>> TABLE `branches_config` (
>>   `id` int(11) NOT NULL AUTO_INCREMENT,
>>   `bid` int(11) NOT NULL,
>>   `client` varchar(64) NOT NULL,
>>   PRIMARY KEY (`id`),
>>   KEY `branches_config_ibfk_1` (`bid`,`client`),
>>   CONSTRAINT `branches_config_ibfk_1` FOREIGN KEY (`bid`, `client`)
>> REFERENCES `branches` (`id`, `client`)
>> )
>
>
> For the 'branches' table, 'id' is a part of the primary key and also
> referred by 'pid'. I tried to define 'id' as a Set attribute to satisfy the
> one-to-many self-reference. But Set attribute cannot be a part of primary
> key. Could you tell me how to define such a relationship?
>
> For the 'branches_config' table, (bid, client) refers 'branches' (id,
> client). How to define the reference between two composite key?
>
> Thank you and I look forward to hearing from you.
>
> Best wishes,
> Agatha
> _______________________________________________
> ponyorm-list mailing list
> ponyorm-list at ponyorm.com
> /ponyorm-list
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: </ponyorm-list/attachments/20181011/ed2c290e/attachment.html>