Change primary key limitation for MS SQL Server sinks

Answered

Follow

Marius Dammen

• April 20, 2020 13:16

If a table (e.g. a staging/interface table) has a auto incrementing id set as the primary key (PK), Sesam cannot be used.

It cannot supply the value, since it is generated, and if the PK-column is not specified in the config, it is auto discovered via the table metadata (and fails because of a missing PK).

The main goal of Sesam is to keep the data in equal state, but as an integration engine, it would be nice to support this pattern.

1

• Official comment

  

  Geir Ove Grønmo

  • October 16, 2020 11:19

  The "sql" sink requires the table to have an immutable primary key in the entity that is reflected in the table. Using an auto incrementing column for primary key does not work as the sink is then not able to relate the entity. In general the sink need to have full control over the columns in the table – and no other process should be writing to it. A workaround might be to write into a separate table and then use triggers, or alternatively have a writable view on top of the existing table.

  If you have any ideas how to do this then consider implementing it as a microservice. This is a often asked question and people seem to have similar requirements, so there might others interested in such a microservice.

  Comment actions Permalink

Please sign in to leave a comment.