Prevent NULL in one-to-one Doctrine relationship

[NotionCommotion]

The following metadata...

<?xml version="1.0" encoding="utf-8"?>
<doctrine-mapping xmlns="http://doctrine-project.org/schemas/orm/doctrine-mapping" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://doctrine-project.org/schemas/orm/doctrine-mapping https://www.doctrine-project.org/schemas/orm/doctrine-mapping.xsd">
  <entity name="NotionCommotion\App\Account\PublicAccountIdIncrementor" table="public_account_id_incrementor">
    <id name="account" association-key="true"/>
    <field name="chart_id" type="integer">
      <options>
        <option name="default">0</option>
      </options>
    </field>
    <field name="source_id" type="integer">
      <options>
        <option name="default">0</option>
      </options>
    </field>
    <one-to-one field="account" target-entity="NotionCommotion\App\Account\Account" fetch="LAZY" inversed-by="idPublicIncrementor">
      <join-columns>
        <join-column name="id" referenced-column-name="id" nullable="false"/>
      </join-columns>
    </one-to-one>
  </entity>
</doctrine-mapping>

<?xml version="1.0" encoding="utf-8"?>
<doctrine-mapping xmlns="http://doctrine-project.org/schemas/orm/doctrine-mapping" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://doctrine-project.org/schemas/orm/doctrine-mapping https://www.doctrine-project.org/schemas/orm/doctrine-mapping.xsd">
  <entity name="NotionCommotion\App\Account\Account" table="account">
    <id name="id" type="integer"/>
    <one-to-one field="idPublicIncrementor" target-entity="NotionCommotion\App\Account\PublicAccountIdIncrementor" mapped-by="account"/>
  </entity>
</doctrine-mapping>

creates the following methods...

<?php
class Account
{
    /**
     * Set idPublicIncrementor.
     *
     * @param \NotionCommotion\App\Account\PublicAccountIdIncrementor|null $idPublicIncrementor
     *
     * @return Account
     */
    public function setIdPublicIncrementor(\NotionCommotion\App\Account\PublicAccountIdIncrementor $idPublicIncrementor = null)
    {
        $this->idPublicIncrementor = $idPublicIncrementor;

        return $this;
    }

    /**
     * Get idPublicIncrementor.
     *
     * @return \NotionCommotion\App\Account\PublicAccountIdIncrementor|null
     */
    public function getIdPublicIncrementor()
    {
        return $this->idPublicIncrementor;
    }
    
}

As seen, Account::idPublicIncrementor allows NULL value.  Is this by design since PublicAccountIdIncrementor's primary key is Account's primary key?  If not, how do I instruct Doctrine to prevent NULL?

[requinix]

Using v2.x? I found a @todo in the part of the code that determines whether the association is nullable (EntityGenerator::isAssociationIsNullable). The only criteria it can currently test for to say it's not nullable is whether the association is to an <id> or if it has <join-column> that's not nullable - neither of which you can use.

So I guess the answer is no.

[NotionCommotion]

3 hours ago, requinix said:

Using v2.x? I found a @todo in the part of the code that determines whether the association is nullable (EntityGenerator::isAssociationIsNullable). The only criteria it can currently test for to say it's not nullable is whether the association is to an <id> or if it has <join-column> that's not nullable - neither of which you can use.

So I guess the answer is no.

Non-Doctrine specific, does this type of scenario often reflect foreign key constraints?  For instance, one can't create a PublicAccountIdIncrementor until the account PK is known but one doesn't know this PK until the account is created (assuming using auto incrementing which I happen not to be doing, but seems to be the norm).

[requinix]

With a foreign key, the foreign entity has to exist first.

I don't really know what's going on with these ID incrementor things... so either you don't have the circular dependency you think you do, or you've created an impossible situation you will have to resolve.

[NotionCommotion]

2 hours ago, requinix said:

With a foreign key, the foreign entity has to exist first.

I don't really know what's going on with these ID incrementor things... so either you don't have the circular dependency you think you do, or you've created an impossible situation you will have to resolve.

No, there are no circular dependencies or impossible situations, and all is good.  For a few rare cases, I've needed to disable FK before inserting a record, but this is not one of those cases.  Was just saying that with a bi-directional 1-to-1 where the owning side's PK is the other's side PK, one would have to from a SQL's perspective insert the other's side record first.

Regarding those ID incrementor things, you can blame jacques for them.  NEVER EXPOSE A PRIMARY KEY OR HELL WILL RAIN!!!  But great big random numbers are not good from a ux perspective, and should not be needed for some applications.  Trying to meet both needs was definitely more than I bargained for, and in hindsight wish I didn't follow that advice.

[requinix]

A trick I've seen in the wild is encryption. Take your normal numbers, transform a bit to get a nice string, encrypt, and transform again to get some usable string.

But whether something is a primary key or not doesn't matter here. A PK just means it's unique and is the main method used to identify a record. So you'd pick one table as the "main" table, insert there first, have it generate the IDs automatically, then use it for the second table where it's both the PK as well as an FK.

[NotionCommotion]

Your right, it wasn't PK's that I was thinking about but edge cases.  All people must have a favorite memory and all memories are unique. and belong to a given person.

[kicken]

On 4/5/2019 at 7:44 PM, NotionCommotion said:

For a few rare cases, I've needed to disable FK before inserting a record

I don't know why you'd need to disable a foreign key to insert a record.  Usually that's something you'd only do when bulk-loading things with pre-determined IDs (eg, restoring a data backup)  Otherwise you'd do an Insert, Update pattern.  Going with your memory example and assuming you have a FavoriteMemory column in the person table, you'd just do:

1. Add person record with a NULL favorite memory
2. Insert new memory
3. Update person with new memory id.

The memory column just needs to be nullable, then you should be able to insert the person without disabling the foreign key.

You can't really make the memory column NOT NULL in that kind of situation, even if ideally it would be. You'll just have to enforce the not null constraint elsewhere.

 

I've been in that kind of situation before, primarly with things like addresses, emails, phones.  For example a user could have multiple addresses but one of them can be the default.  Rather than have an IsDefault flag on each address I add a DefaultAddressId reference on the user.  The user registration process acquires both details on the form then processes as above.  Insert user with null default,  Insert address, update user default.

 

[NotionCommotion]

I've only had to do so when adding a new account which needs to have some default value.  You are right, doing so is not necessary if I make the column nullable, but what is wrong with doing it this way?  If using autoincrement for account (which I happen not to be doing), I would just need to temporarily use zero as the account ID.
 

            $this->pdo->beginTransaction();

            $accountId=$this->generatePK();

            $this->pdo->exec('SET FOREIGN_KEY_CHECKS=0');

            $this->pdo->prepare('INSERT INTO virtual_lan (id, name, description, account_id) VALUES (null, ?, ?, ?)')
            ->execute(["Default", "Initial Virtual LAN", $accountId]);

            $accountData['virtual_lan_id']=$this->pdo->lastInsertId();

            $this->pdo->exec('SET FOREIGN_KEY_CHECKS=1');

            $this->pdo->prepare('INSERT INTO account (id, name, virtual_lan_id, foo, bar) VALUES (:id, :name, :virtual_lan_id, :foo, :bar)')
            ->execute($accountData);

            $this->pdo->commit();

 

Edited April 7, 2019 by NotionCommotion

[kicken]

1 hour ago, NotionCommotion said:

but what is wrong with doing it this way?

You may as well not even have the foreign key if your just going to disable it.

From the manual

Quote

Note

Setting foreign_key_checks to 1 does not trigger a scan of the existing table data. Therefore, rows added to the table while foreign_key_checks=0 will not be verified for consistency.

Since it never checks if what you entered is valid it means the field could contain any garbage data it wanted so you can't trust it.

With a nullable field that is updated however you can generally trust that if the field has a non-null value that the value is valid and exists in the other table.

 

[NotionCommotion]

3 hours ago, kicken said:

You may as well not even have the foreign key if your just going to disable it.

With a nullable field that is updated however you can generally trust that if the field has a non-null value that the value is valid and exists in the other table.

Hum,  On one hand, I feel that adding an account is a fairly rare activity and code could be located at a single location to ensure quality.  On the other hand, I definitely see your point.