Swapping OneToOne relations results in am UNIQUE constraint failed error

[Langstra]

Describe the bug
When replacing an entity on a OneToOne relation the ORM first tries to insert the new entity. This results in an UNIQUE constraint failed error. You'd expect the ORM either to first set the foreign key to NULL or to delete the old entity.

Stack trace

UniqueConstraintViolationException: insert into `user` (`name`, `project_id`) values ('Johnny', 1) returning `id` - SQLITE_CONSTRAINT: UNIQUE constraint failed: user.project_id
        at SqliteExceptionConverter.convertException (/home/langstra/code/mikro-orm/packages/sqlite/src/SqliteExceptionConverter.ts:26:14)
        at SqliteDriver.convertException (/home/langstra/code/mikro-orm/packages/core/src/drivers/DatabaseDriver.ts:262:50)
        at /home/langstra/code/mikro-orm/packages/core/src/drivers/DatabaseDriver.ts:267:18
        at async SqliteDriver.nativeInsertMany (/home/langstra/code/mikro-orm/packages/knex/src/AbstractSqlDriver.ts:363:17)
        at async ChangeSetPersister.persistNewEntity (/home/langstra/code/mikro-orm/packages/core/src/unit-of-work/ChangeSetPersister.ts:107:17)
        at async ChangeSetPersister.executeInserts (/home/langstra/code/mikro-orm/packages/core/src/unit-of-work/ChangeSetPersister.ts:38:7)
        at async ChangeSetPersister.runForEachSchema (/home/langstra/code/mikro-orm/packages/core/src/unit-of-work/ChangeSetPersister.ts:86:7)
        at async UnitOfWork.commitCreateChangeSets (/home/langstra/code/mikro-orm/packages/core/src/unit-of-work/UnitOfWork.ts:872:5)
        at async UnitOfWork.persistToDatabase (/home/langstra/code/mikro-orm/packages/core/src/unit-of-work/UnitOfWork.ts:811:7)
        at async SqliteConnection.transactional (/home/langstra/code/mikro-orm/packages/knex/src/AbstractSqlConnection.ts:51:19)
        at async UnitOfWork.doCommit (/home/langstra/code/mikro-orm/packages/core/src/unit-of-work/UnitOfWork.ts:356:9)
        at async UnitOfWork.commit (/home/langstra/code/mikro-orm/packages/core/src/unit-of-work/UnitOfWork.ts:324:7)
        at async SqlEntityManager.flush (/home/langstra/code/mikro-orm/packages/core/src/EntityManager.ts:953:5)
        at async Object.<anonymous> (/home/langstra/code/mikro-orm/tests/issues/GHxxxx.test.ts:81:7)

To Reproduce
Steps to reproduce the behavior:

1. Checkout PR test: add 1:1 swapping without flushing in between #3615
2. Run test case GH3614
3. ...

Expected behavior
You'd expect the ORM either to first set the foreign key to NULL or to delete the old entity.

Additional context
Also tried mysql driver by the way.

Versions

Dependency	Version
node	14
typescript	?
mikro-orm	next
your-driver	sqlite

[B4nan]

Regarding the repro, you can't have two 1:1 inverse sides mapped by the same owner - that literally means there is only one value, not two.

// this creates only one user, not two, owner and secondaryOwner will be the same
orm.em.create(Project, {
      name: 'project name',
      owner: {
        name: 'Peter',
      },
      secondaryOwner: {
        name: 'Mary',
      },
    });

Maybe you wanted to have the other side as owning? Also the final reloading wont work as you think, due to propagation project.owner is the new value, not the old one.

Btw here the second line is a no-op, it will be already there due to propagation from the em.create() that has project: 1 in the data:

const newOwner = orm.em.create(User, { name: 'Johnny', project: 1 });
project.owner = Reference.create(newOwner); // <-- this is no-op

But the issue itself is indeed valid.