[Fixed] composite key one-to-one unexpected behavior (adds unique constraint to all fields in composite key)

Question

Look at image.

I have ComponentProviderPrice entiy with composile PK (componentid, providerid)
I have ComponentProviderPriceDetail entiy with composile PK (componentid, providerid)

I need one-to-one between ComponentProviderPrice and ComponentProviderPriceDetail
When I select that relation, editor automaticaly adds uniq constraints
to componentid and providerid

But I dont need that (maybe it must be composite uniq - but better way is not do magic there - cause i can't affect it)
When I remove uniq (unchecking checkboxes) my relation comes one-to-many.
Now I need manualy edit skipper file (after save project) and manualy fix relations before export to ORM.

And i have another strange behavior when save/load project.
1) I leave uniq constraits and save project.
2) When loading i see one-to-one between component and ComponentProviderPriceDetail. And same between provider and ComponentProviderPriceDetail

Plz. fix this. Too muth magic for me.

Answer 1

Hello,

one-to-one relation is created because of Unique constraint. This is how one-to-one works ;-).

For one-to-one you need to tell database that only ONE object can point to another object. And this ONE means that it's UNIQUE.

So there is nothing to "fix". This is the correct behavior.

In case you think there is a better way, you can also try to edit your schema files manually and write definition manually. This can help you to understand how things work.

Then you can compare your manual schema and schema exported via Skipper. If you will find another/better way, feel free to post both of these schema files here and we can help you with that.

Comments

Primary key is composite.
Skipper create UNIQ constrait for each field, not COMPOSITE UNIQ constrait.
Try to show you

Here link to skipper project.
https://drive.google.com/file/d/1H5wkhzh0qOkViPmkK15S-GonqWb0GXrq/view?usp=sharing

And step-by-step project changes to you can reproduce
https://drive.google.com/file/d/1ZpgPpdB9BdQL7MiYjnzLoHW9Y2CAk2im/view

Maybe it can help to understand me
That what i need:

This is corrent mapping in doctrine. But there is no way do that with skipper.

---

Thanks for the detailed answer and sorry for late reply. I completely missed the notification. I will check it today and let you know.

---

I just checked your testing project and you're right. Now I understand where is the problem.

I have to check internal logic but I believe that we will be able to fix this behavior.

---

After checking it more deeply, I'm not sure we will be able to fix that issue.

The "problem" is that CompositeProviderPrice is using referred componentid and providerid like ComponentProviderPriceDetail, which use it as PK, together with FK to CompositeProviderPrice and together with standalone FK to Provider and Component.

As you correctly find out, changing multi-fk association to ComponentProviderPrice change UQ flag also for associations to Provider/Component and vice-versa. Unfortunately, this is because of inner design where associations one-to-one vs one-to-many are managed through UQ flag, which was sufficient for all the years until now in your project.

The same issue can happen in BestComponentProvider, where you're sharing componentid and providerid for component/provider associations together with ComponentProviderPrice (in case you would decide to make one of the associations one-to-one).

Unfortunately, this is something we can't easily fix. This is something that would require a deep rework of some inner logic, but also something, which causes that a lot of other tasks will because much harder (because UQ switch will not work). I need to discuss it with my team but I can't promise any ETA for reworking this issue.

On the other hand, the solution for you is pretty easy (and based on my modeling experiences also the better one). You can introduce unique IDs for every entity you want to store and instead of sharing componentid and providerid in several entities, use these unique IDs and use componentId and providerid only as standalone foreign keys (maybe also introduce ComponentProvider entity, instead of referring with multi-pk).

I will let you know in case we will find a way how to fix that. Thanks for understanding.

---

PS: Another "hack" solution would be to use one-to-many association in between ComponentProviderPriceDetail and ComponentProviderPrice instead of one-to-one.

---

PS2: The reason for all this logic is that doctrine2 allows definition for "nullable" and "unique" flags for every column in @joinColumn (https://www.doctrine-project.org/projects/doctrine-orm/en/2.6/reference/annotations-reference.html#joincolumn)

So, Skipper needs to allow connect "UQ" and "NN" flags to associations when selecting "one-to-one" vs "one-to-many" and when selecting "parent-required" vs "parent-optional".

All of this works fine until today ;-)

Maybe the solution will be in separating logic for single-pk associations, where current logic is corrent and multi-pk associations. Maybe only for multi-pk on foreign keys. I'm not sure.

For multi-pk associations it's still necessary to be able to set nullable and unique per-column, where UQ and NN works fine.

@ORM\JoinColumn(name="component_id", referencedColumnName="id", nullable='false", unique="true"

The only issue seems to be in connection between UQ/NN flags and these multi-pk associations. So, maybe it will be easier to fix that than I originally thought.

Anyway, I will pass all my notes (which I post here) to my colleagues and will discuss it. I will let you know.

---

Thank you for your work. Maybe my solution is not popular. I use composite PK instead IDs cause I always use componentid and providerid to access the price. In my app I have no other use cases.So additional ID field will never used for access the entity.
If this issue hard to fix i will have to use ID for these assosiations

---

Give me 24 hours, so I can discuss it with others ;-).

Maybe there will be a way how to fix it easily through existing inner flags. Skipper already internally handles "many-inverse" and "many-owner" flags for MongoDB ODM where associations don't have any fields. So maybe this will be a way also for multi-pk-fk scenarios.

I will let you know.

Answer 2

Hello,

I have great news for you ;-). After a day of struggling with this issue, we have found a solution.

We had to reworked handling of multiple-field associations as I previously expected, but also update load/save and import/export of projects for these association.

Currently, UQ flag is updated only for the single-field association but for multi-field isn't modified (because as you correctly found, it's not a correct to set two independent UQ flags for multi-field FK).

Version with this fix is available here: https://support.skipper18.com/402/downloads-skipper-beta

I tested it on your project and it's working ok (it's necessary to update existing associations first to correct one-to-many and one-to-one cardinality).

Please test it and let me know if everything works for you as expected.

Thanks

Comments

Just tested it. All work fine for me.
Thank you!

---

Perfect. Thanks