Hello community!
Using SQL Server, We are setting up data replication of alarm_event_data.
It seems to be impossible because this table do not have a primary key.
Is there a problem if I add a pk_id autoincrement column as a primary key?
That’s weird. I have some alarm_event_data tables, and they all have a primary key field named id
The column name id is not a primary key. It is a link with the table alarm_event.
A primary key must be different for all rows in a table. This is not the case here:
Use a composite primary key of id+propname. Unless you are using old MySQL, which can’t do that.
1 Like
May I ask which SQL platform you are using and if you were able to make the id column to be Composite Primary Keys?
I am using MySQL 8.0.26 and having this same issue.
MySQL 8.0 documentation suggests you should be able to: MySQL :: MySQL 8.0 Reference Manual :: 8.3.6 Multiple-Column Indexes
Hi Phil,
I've a similar issue with the Ignition Historian tables, no primary key for replication available in a MSSQL Server 2019:
- sqlth_partitions
- sqlth_sce
How do I've to configure your proposed solution?
Add a KP ID column or a composite primary key column in those tables?
Will the extra PK in both tables influence the normal functionality of the Ignition Historian?
Thanks for you reply,
Dries
You can't have two primary keys. Replace the original primary key with a composite. Perhaps recreate the original as a non-clustered index.
As for operations, MSSQL's memory consumption for caching the primary key will increase.
Thanks Phil,
I could solve this by adding an IDENTITY PRIMARY KEY "id" in the two tables.
This way standard MSSQL replication can be used, because now all Ignition Historian tables have a primary key.
Kind regards,
Dries
Sure. Just keep an index on t_stamp, at the very least. (Probably also on tag_id.)