Support relations on unique keys (that are not primary key)

[aschempp]

I have found several hints (e.g. #3135 (comment)) that it is not possible to use a non-primary key as a reference column.

I wonder if there are technical reasons that make this impossible, or it's just that this has not (yet) been implemented?

I'm working on migrating our existing model implementation into Doctrine. It works perfectly except for one case: On one table we have a regular auto-incrementing ID, plus a column with a UUID. We use the UUID as reference column because it will allow to merge tables from different installations (the table holds a UUID to filesystem path mapping).
I'm willing to work on a PR to support the unique reference case, but obviously would be interested if it makes sense at all ^^

/cc @leofeyer @Toflar @discordier

[MacDada]

http://docs.doctrine-project.org/en/latest/reference/limitations-and-known-issues.html#join-columns-with-non-primary-keys

I wish there was more info in the docs – as I am curious myself (legacy app, looooots of tables pointing to non-id columns).

[Ocramius]

This is not currently possible, as references in the entity graph are kept only by identifier inside the UnitOfWork.

[MacDada]

@Ocramius Just for the clarification, I know nothing can be done in the near future.

Every entity must have an identifier. Why be limited to only one? Could it be (re)implemented to support multiple identifiers? Even if that took a major redesign? Like for version 3 or something? ;-)

I mean, that's what I need: I want to identify my entity sometimes with one field and other times with another field (both are unique ofc).

[aschempp]

If I understand correctly, it cannot be solved without eager-loading the related record, right? Similar to STI with non-leaf entities? As soon as the entity is fully loaded, we do know what properties are unique and can add additional entity graphs for them?

[clevercodenl]

has this problem been fixed ?

[Ocramius]

No, it's not going to be fixed either due to associations requiring stable identifiers (PKs)

[clevercodenl]

@Ocramius, thank you for your answer!
And is there a maybe another way to fix this problem ? Or should this not be seen as a problem, thus we should make sure that all associations refer to a column that is a primary key ?

[Ocramius]

It is not a problem: it is an architectural decision, and a conscious limitation.

[oojacoboo]

No, it's not going to be fixed either due to associations requiring stable identifiers (PKs)

@Ocramius so a unique column isn't a "stable identifier"? I'm not sure I understand the technical limitation in choosing not to support this. And saying it's an architectural decision isn't really valid, unless it's just opinionated. Forcing all relationships to be based on a PK, while suitable for most situations, leaves out very valid use cases from db design.

I realize that Doctrine is taking an ORM -> database approach, instead of a database design first approach, which I patently disagree on this stance, but nonetheless, are there technical reasons to not support this, or is it just purely opinionated?

[FrancescJR]

+1, seems totally arbitrary. But well. I am wandering though, it looks I can just ignore the validate? or something will go terribly wrong? for one, I don't like to rely on Dcotrine doing magic for me, I like to have the checks on place instead of setting validation check at teh database level (which I believe they shoudl enver be there since it just adds workload and duplication with no benefits)

[oojacoboo]

@FrancescJR I'm not sure I understand what you're saying here. But, your database should be your source of truth. No application code, should ever be relied on for data integrity, ever. That said, there are varying levels of "validation" for data. Personally, I don't want my domain layer handling any validation. Validation should be handled as early as possible after input. With a clean model and ORM layer, should errors occur on your strict database/persistence layer, those errors should be addressed within your validation layer after input.

[FrancescJR]

What I wanted to ask is if I could just ignore this error, or something might go wrong. In my case, the table has actually two primary keys (the ORM ID, and the one I use for the relationship (one will be removed eventually and I will rely only one) so they are both unique.

I got this validate error, but the rest seems to work quite fine, fetching entities though this relationship etc. Or should I expect some unexpected behaviour for not following the best practice?

(about the other, the database is actually my source of truth, but I don't think there is need to add the same constraints all around, but anyway, primary keys are actually not a good example about that, it doesn't apply, you're actually right and I should not have said that)

[oojacoboo]

@FrancescJR I have no idea what error you're referring to. This ticket is in regards to Doctrine not supporting relations on non-PK columns/properties. We do not use a Doctrine first approach - never will. I think that's dumb and careless. Therefore, the overall design of the database will never be 1:1 with Doctrine and don't feel the need for that to be the case. It is, however, quite close. But, since we don't need Doctrine to generate a database schema for us, it's not 100% and, again, probably never will be. Therefore, having constraints at the database level, that are not implemented within Doctrine is a non-issue and receives full support from me, if that's your question. Just realize that they're not going to be 1:1 in this case.

[FrancescJR]

I am refering to this "warning" when you use the doctrine's command "validate":
The referenced column name 'xxxx' has to be a primary key column on the target entity class 'xxxxx'.

If it's ok to not have the database and Doctrine 1:1 (and probably never will be, as you say) then why there is this validate command at all?

I don't want anybody to support or not support anything, and even less if it has to become such an annoyance, god forbid. I also have no idea who's "we" when you say "we", and you assume you're right when all you have is just an opinion. I was just expressing my frustration. But of course it's my fault by being confused by the command's output. I'll know better next time. Good bye.

[oojacoboo]

"We" is the company that I'm with.

That error, I don't recall exactly, is most likely because Doctrine refuses to setup the relationship internally. That's completely different from what I was referring to in terms of using the database for integrity constraints vs adding those relationships to the model layer.