I have replicated all the tables from the Business Data database to my instance of SQL Server 2005. They did not create primary keys on the tables, so I am adding them on the replicated database. I was trying to save one of the tables after adding a new primary key and got this error message:
'BuildingValues' table
- Unable to create index 'PK_BuildingValues'. CREATE UNIQUE INDEX terminated because a duplicate key was found for object name 'dbo.BuildingValues' and index name 'PK_BuildingValues'. The duplicate key value is (2). Could not create constraint. See previous errors. The statement has been terminated.
The problem is that there is nothing displayed in the Indexes folder for this table in Object Viewer (or any of the other folders for this table), so how would I know an index already existed? The index shows up for this table on the Publisher database, but not the Replicated database. Is this normal?
Sounds like you only replicated the data over without transfering other objects (i.e. pk, constraints, indexes, etc.). The error here is that your current data is not qualified for a pk constraint - pk creates an unique key/constraint underneath, thus, the values cannot be duplicated.To find the dupes, try:
select [your_pk_col]
from tb
group by [your_pk_col]
having count(*)>1
Resolve that and you should be able to create your pk.
Indexes should get created on the subscriber if you configure it during publication creation - go to the object properties.|||
Thank you very much for your answer. The articles at the Publisher were configured to replicate all the keys and constraints to the Subscriber. You were right about the cause of the problem. The index could not be created, because of duplicate values in the column on the Subscriber. Once I added another column to make it unique, the primary key worked fine. Thanks again.
No comments:
Post a Comment