Instance_Del - Foreign Key violation

[smschissler]

DBADash Version: 2.49.0.0

When running:

EXEC dbo.Instance_Del @InstanceID = 1, @HardDelete = 1

When there are records in the dbo.IdentityColumns table or the dbo.IdentityColumnsHistory tables for that InstanceID, the following foreign key violations will be generated.

Msg 547, Level 16, State 0, Procedure dbo.Instance_Del, Line 414 [Batch Start Line 2] The DELETE statement conflicted with the REFERENCE constraint "FKIdentityCDatab__1E3A7A34". The conflict occurred in database "DBADashDB", table "dbo.IdentityColumns", column 'DatabaseID'.

Msg 547, Level 16, State 0, Procedure dbo.Instance_Del, Line 418 [Batch Start Line 2] The DELETE statement conflicted with the REFERENCE constraint "FKIdentityCDatab__2022C2A6". The conflict occurred in database "DBADashDB", table "dbo.IdentityColumnsHistory", column 'DatabaseID'.

The following delete commands: DELETE dbo.IdentityColumnsHistory WHERE InstanceID = @InstanceID

DELETE dbo.IdentityColumns
WHERE InstanceID = @InstanceID

need to be added prior to:

DELETE dbo.Databases 
WHERE InstanceID = @InstanceID

Cheers, Stephen Schissler

[DavidWiseman]

Thanks for reporting this issue! If you are interested in contributing, you could create a pull request to fix this issue. If not I'll probably take a look at it next week. Either way thanks for the detailed report and fix. 😊

[smschissler]

Sure, I'm new to the GIT process. I'll do a pull request with the change. Thanks for making this application open source.

-Stephen

On Fri, Dec 1, 2023, 5:19 PM David Wiseman @.***> wrote:

Thanks for reporting this issue! If you are interested in contributing, you could create a pull request to fix this issue. If not I'll probably take a look at it next week. Either way thanks for the detailed report and fix. 😊

— Reply to this email directly, view it on GitHub https://github.com/trimble-oss/dba-dash/issues/777#issuecomment-1836855411, or unsubscribe https://github.com/notifications/unsubscribe-auth/BEMTH7LB6N7TSDANNR5QVXLYHJJVZAVCNFSM6AAAAABADIB2K6VHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMYTQMZWHA2TKNBRGE . You are receiving this because you authored the thread.Message ID: @.***>

[DavidWiseman]

Thanks. It's worthwhile learning Git as pretty much all software development relies on it now. It's also useful for DBAs that support those development teams. If you need help, let me know.

I'll also look at adding a test to the GitHub actions to hopefully prevent it from getting broken in the future. #780

[DavidWiseman]

Hi, let me know if you are still interested in doing a PR for this. If not, I'm happy to do it. I've created a test for it that should prevent it getting broken again in future.

[smschissler]

Hi David,

I have not had time to look into how to do the pull request, so you should probably do it.

Thanks! Stephen

On Wed, Dec 13, 2023 at 3:21 AM David Wiseman @.***> wrote:

Hi, let me know if you are still interested in doing a PR for this. If not, I'm happy to do it. I've created a test for it that should prevent it getting broken again in future.

— Reply to this email directly, view it on GitHub https://github.com/trimble-oss/dba-dash/issues/777#issuecomment-1853458583, or unsubscribe https://github.com/notifications/unsubscribe-auth/BEMTH7PLUITYHB27OU6JEM3YJFQRJAVCNFSM6AAAAABADIB2K6VHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMYTQNJTGQ2TQNJYGM . You are receiving this because you authored the thread.Message ID: @.***>

[DavidWiseman]

No problem. The fix will be included in the next release.

[DavidWiseman]

This is included in version 3.0. Note, that there is an upgrade to .NET 8. 🚀