DEFAULT constraint is not renamed when dropping a ledger table

Gregory Spinney 20 Reputation points
2023-01-23T18:49:47.3366667+00:00

This isn't really a question. It's also not really an "issue" per se as I was just learning about Ledger tables in Azure SQL (and I guess 2022) out of sheer curiosity. I just wanted to point out a minor bug, since I'm not seeing anyone else having noticed it. Probably because it's a very new feature, doubt it's been widely adopted yet, and where it is it's unlikely anyone's needed to do what I tried to do - yet.

If you "drop" a ledger table, by design (more like "duh, that's the point of it") it isn't actually dropped - it gets renamed, along with at least the primary key constraint. But it would appear any default constraint is NOT renamed.

I created a test table - again, just playing around with 'em. Made a schema named "ledger" with a table named "heath" (yeah, I... sorry). Made it an append-only ledger table, verified it's true to form, update/delete blocked. Then half-suspecting what would happen, dropped it. Unsurprisingly it was renamed e.g. MSSQLDroppedLedgerTable_heath_<blah> and I cannot get rid of it (not supposed to, I know). The PK and auto-created view were both renamed along with the table, with similar "MSSQLDropped..." prefixes - good. But a default constraint on it was not; it was and still is just "df_heath_datetime". This is looking in sys.objects for anything named "%heath%" ... I see the table, the view, and the PK, all renamed, but the default constraint was not.

I have not checked similar types of constraints (e.g. CHECK), but I suspect 50/50 they might have the same glitch.

I also can't move it to another schema, though whether that's by design or not, it's acceptable either way far as I'm concerned; would be nice, but I can see the arguments against it, too. But that it doesn't rename all constraints - that has to be a bug/oversight. Minor one, and like I said for my purposes it's inconsequential - why I'm posting this here instead of opening an actual ticket. But someone's bound to eventually. I can't imagine it's "by design" since it IS renaming the PK.

I guess if there's a question in all this, it would be "where do I report a relatively obvious, simple, and easy-to-replicate, but mostly-innocuous bug that I don't immediately care about?"

Azure SQL Database
0 comments No comments
{count} votes

Accepted answer
  1. Erland Sommarskog 99,371 Reputation points MVP
    2023-01-23T22:34:00.2633333+00:00

    Good catch!

    I tested with a CHECK constraint as well, and it is being renamed as well. But the default constraint is not. I did not test foreign keys.

    You can report bugs here: https://feedback.azure.com/d365community/forum/04fe6ee0-3b25-ec11-b6e6-000d3a4f0da0 to make Microsoft aware of this issue.

    1 person found this answer helpful.

0 additional answers

Sort by: Most helpful