IDENTITY_INSERT issue when primary key uses different column/columns

[maxim-bandurko-lsvt]

At Spring Boot 2.4.0 (SNAPSHOT) when trying to insert new record to table that has identity column not the same as primary key uses, it produces:

io.r2dbc.mssql.ExceptionFactory$MssqlDataIntegrityViolationException: Cannot insert explicit value for identity column in table 'Some' when IDENTITY_INSERT is set to OFF.
        at io.r2dbc.mssql.ExceptionFactory.createException(ExceptionFactory.java:100) ~[r2dbc-mssql-0.8.3.RELEASE.jar:0.8.3.RELEASE]
        at io.r2dbc.mssql.ExceptionFactory.createException(ExceptionFactory.java:181) ~[r2dbc-mssql-0.8.3.RELEASE.jar:0.8.3.RELEASE]
        at io.r2dbc.mssql.RpcQueryMessageFlow.lambda$exchange$1(RpcQueryMessageFlow.java:148) ~[r2dbc-mssql-0.8.3.RELEASE.jar:0.8.3.RELEASE]
...

To reproduce:

CREATE TABLE Some (
	id bigint NOT NULL IDENTITY(1,1),
	a bigint NOT NULL,
	b bigint NOT NULL
);
GO

ALTER TABLE Some ADD CONSTRAINT PK__Some__F95C786A090ED377 PRIMARY KEY (a, b);
GO

@Data
@Table("Some")
public class SomeDAO {
    
    @Id
    @Column("id")
    private long id;

    @Column("a")
    private long a;

    @Column("b")
    private long b;

}

SomeDAO entity = new SomeDAO();
entity.setA(1);
entity.setB(2);

repository.save(entity);

[mp911de]

Spring Data R2DBC includes all non-null columns in the insert statement. Since id is of a primitive type, it’s value is zero, hence it gets included in the insert. Please change the type to Long.

[maxim-bandurko-lsvt]

@mp911de I see, makes sense! Closing ticket. Thank you!

[mp911de]

After reiterating on this topic, we should apply an optimization. Our IsNewStrategy already considers entities whose Id is a primitive type that is set to zero new. According to that, it makes sense to expect the Id will be generated and that we should not insert the Id property but rather exclude it from the INSERT field list. I filed #402 to track those efforts.

[maxim-bandurko-lsvt]

That would be awesome!