- UNIFYBroker Forum
-
Questions
Cannot upgrade IsB database - Duplicate key value
Upgrade from 5.1 to 5.2 failed with a SQL error so I elected to run the database update script manually. This is the error:
Msg 1505, Level 16, State 1, Line 1
The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.CollectionKey' and the index name 'DF_CollectionKey_Caption'. The duplicate key value is (HomePostCode).
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.
The statement has been terminated.
I have both "HomePostCode" in a connector and "HomePostcode" (lower-case "c") in an adapter, and IdB did not prevent this happening. I'll rename one of them if I have to but am worried there might be others (lots of schema in this solution).
Shouldn't this script be using the attribute guids anyway?
Answer
Started the process of renaming one attribute but as I feared there are more. It always seems to be a case difference. So IdB is allowing the different case but the DB upgrade script is not.
Hi Carol
The uniqueness constraint that is causing you issues was added in 5.2 as a solution to another issue, but as you've noticed doesn't regard case when comparing strings. Renaming fields to be unique/use the same casing is a workaround, however due to the casing problem I think the original reason the constraint was added ended up being solved in code. I'll have a look into this. It may be that the constraint is not actually needed anymore. I'll let you know what I find.
Hi Carol
Turns out that, yes, the DF_CollectionKey_Caption constraint isn't actually required. If it was created in your database, go ahead and remove it. If it wasn't then no further action is required, the rest of the upgrade script will have executed correctly.
Where would that be? I had a look under dbo.CollectionKey/Constraints but there's nothing there.
This returns nothing:
SELECT * FROM sys.key_constraints WHERE name = 'DF_CollectionKey_Caption'
OK I think I understand you now - I have commented out the last 5 lines of the IdentityBroker Upgrade.sql script and run it again - now I don't get errors.
This problem was also preventing the IdB install completing when I chose "Automatic upgrade".
It actually would have been located under dbo.CollectionKey/Keys, strangely enough. It would have only been created if you change the contents of that table so the constraint was satisfied.
Customer support service by UserEcho
Hi Carol
Turns out that, yes, the DF_CollectionKey_Caption constraint isn't actually required. If it was created in your database, go ahead and remove it. If it wasn't then no further action is required, the rest of the upgrade script will have executed correctly.