Updating a `HasOne` (1-1) relation errors with unique constraint failed

[trepidacious]

Description

Trying to update the contents of a HasOne field to a new entity fails, producing a "UNIQUE constraint failed" SQL error on the foreign key.

My motivation here is being able to use a nested ActiveModel to completely replace an entity and all relations in one operation, where this involves replacing the existing HasOne fields. In my application this is when an entity is replaced with new data imported from an external file, so rather than wanting to update parts of an existing entity I just want to basically repeat the original import process, but replacing an entity at an id. This way I can just use the same process of building an ActiveModelEx and saveing it both for the initial import, and any later import that will update the db entity in-place.

Steps to Reproduce

Run the minimal reproduction on github, this should produce the error.

The steps are essentially:

1. Create an entity with a HasOne field. In the repo, this is a User that has one Profile.
2. Insert an instance of User, with the Profile field populated (I did this via ActiveModelEx, I believe the same thing happens if using ActiveModel::builder()).
3. Try to update that User, with new data in the Profile field (I did this by creating a new ActiveModelEx with the same id, and using HasOneModel::set() to set a new Profile in the HasOne field).

Expected Behavior

The update in step 3 deletes the old Profile and creates a new one linked to User. This would be similar to how HasManyModel::replace_all works. This would make sense to me since it would mean HasOne basically provides the same functionality as HasMany, but acting as a collection of 0-1 entities, where HasMany allows a collection of 0-n entities.

Actual Behavior

The update fails with:

Error: Query(SqlxError(Database(SqliteError { code: 2067, message: "UNIQUE constraint failed: profile.user_id" })))

Reproduces How Often

Happens every time on Postgres with a real project, and on Sqlite in memory in the minimal reproduction on github.

Workarounds

I've tried some different cases - updating from a User with one Profile to a different one fails, but if we start from a User with no Profile and add one that works. Leaving the Profile empty also works. Starting with a Profile and going to none specified does work, but this just leaves the old Profile unchanged - see #3060 for a feature request to allow actually deleting the Profile via the User.

Versions

├── sea-orm v2.0.0-rc.38
│   ├── sea-orm-macros v2.0.0-rc.38 (proc-macro)
│   │   ├── sea-bae v0.2.1 (proc-macro)
│   ├── sea-query v1.0.0-rc.33
│   │   ├── sea-query-derive v1.0.0-rc.12 (proc-macro)
│   ├── sea-query-sqlx v0.8.0-rc.15
│   │   ├── sea-query v1.0.0-rc.33 (*)
│   ├── sea-schema v0.17.0-rc.17
│   │   ├── sea-query v1.0.0-rc.33 (*)
│   │   ├── sea-query-sqlx v0.8.0-rc.15 (*)
│   │   ├── sea-schema-derive v0.3.0 (proc-macro)

Database is sqlite in memory, also tested on Postgres 18-alpine docker image.
OS is macOS 15.7.5.