I have a 'many to many' database entity that at one time contained a unique index to prevent duplicate records.

The index was overwritten at some point in time and duplicate records got in to the table.

I have added the unique index back, but I obviously cannot deploy because of the duplicate records.

Is there a way to do a truncate table or drop/recreate table on deployment for this entity?

I am using OS 11.6

Hi Jason,

Take a look at this topic

https://www.outsystems.com/forums/discussion/44778/how-to-truncate-table/


Best Regards,

Leandro.

Hi Jason,

As "outsystems" rule normally, you cannot truncate table with Outsystems! (obviously you can do this if you have direct access to the database and a user with permission to do so)
If wasn't possible execute a "DELETE FROM {YOUR_ENTITY}". you can try cut and past your entity as outsystems way works, will create a new entity (TABLE) in your database. The only problem that you will lose your data.

You first need to deploy code that gets rid of the duplicates (still having the allow dubkeys index)

Then after that you can deploy the change where the index is unique.


Solution

I wrote a query to remove the duplicates from the table manually in SSMS. 

It was just an aggravating situation. The above solutions are all valid in their own way and I appreciate all of your help! It would just be nice if there were more control of the deployment and update of entities similar(not in code but the purpose) to database migrations in OS. If you could add options to an entity to control how it deploys or redeploys that would be very handy!

Anyway, thanks again everyone! I have taken care of the issue.

Solution

Hi Jason,

Changing an index from unique to not-unique and then to unique again, will also in other platforms cause problems. The problem is not the platform. The problem here is the work process of changing this index unique property.

Especially in a situation where you have application and data in production, these type of changes need governance and need up front an impact analysis and a proper step-by-step procedure and sometimes even multiple deployments.

Regards,

Daniel

Daniël Kuhlmann wrote:

Hi Jason,

Changing an index from unique to not-unique and then to unique again, will also in other platforms cause problems. The problem is not the platform. The problem here is the work process of changing this index unique property.

Especially in a situation where you have application and data in production, these type of changes need governance and need up front an impact analysis and a proper step-by-step procedure and sometimes even multiple deployments.

Regards,

Daniel

I never said the platform was a problem. I simply stated that it would be nice to be able to have the option of finer grained control of Entity updates. There can be scenarios where there is a bug in the current version of the application and to fix it may require some T-SQL to be run to correct the database before the Entity is deployed. It can all be done as it stands, but it is very manual and as stated by others in the thread, can cause the table to be dropped and recreated which would cause data loss. The ability to say, hey, I want to modify this entity, and I know it's going to require a couple steps to prepare the table to receive the update, so here are a couple extra steps we'd like to be able to run.