Ecto Constraints when the primary key is a field not auto-generated

[nikolis]

Hello Felas,

I come accross the following problem and I need thoughts.

I have a simple ecto schema with a signle string field seted as the primary key Parent.
This has a has_many relation with a normal ecto schema Child.

I have been creating Childs usually using Parents primary key

%Child{parent_string_field: “some_p_key”}

and ecto hanldes the association within the noramal cast macro and this has been working quit nice for the time.

My problem is what happens if for any reason I would like change the only field and pkey of the parent and would like to re-assign all the children to the new pkey.

Is there any way to handle that gracefully without hacking ?
Is this just a bad design decission ony my part ?
Is there any way to even generate a changeset error automatically when a Parent has already childs associated with it (I have been looking at foreign_key_consraint but it looks like this something you use in the Child Schema would it be possible to propagete that to the Parent changeset somehow)?
Any thougs are welcome.Preformatted text

[dimitarvp]

Could you please format your coding fragments with a single backtick (`) to make it more readable?

[nikolis]

Sure thing

[aseigo]

Best practice is that keys that relate records with each other should be immutable and, in the best case, not have any meaning other than an ID … there are exceptions (aren’t there always!) such as when the foreign key has meaning such as a zip/postal code. Usually this means keys never change.

If you do stay this route, there are two decent approaches I am aware of:

a) a trigger in the database that fires whenever the parent ‘key’ changes, and updates all child items to reflect that change as well. pros: guaranteed to always work no matter the client code; efficient; atomic. cons: likely to be database-specific (how often do you change DB backends, though?), requires admin access to your DB

b) use Ecto.Multi whenever changing the parent ‘key’ field and include both the parent change and an update query to all children. Run it in a transaction so it is atomic.

But yeah, it’s usually not a great design choice to relate records by fields with meaning and esp fields that will change.

[nikolis]

Just to reason behin my decision this suppose to be structure that represents terminology that holds potentional translations for region specific jargons. For example pedestrian might be translated in as in us_en -> to side walk -> road that people without vehicle should use and in uk_en to pedestrian -> road that pedestrians have the highes priority .So since these words might be in any an arbitrary text just annotated accordingly I thought it makes sense to use their names as a primary id since this is not data you would want to change regularly and I can index them using their name with which they should be queried. Only problem is that when a user is trying to change them I canot get ecto to generate a changeset error but rather an exception which is my chief problem.