Is there any in universe explanation as to why the Enterprise always gets to keep its 1701 registry number?
Also I don't recall any other starships from the TNG, DS9, Voyager time period that had this honor bestowed on it. Certainly Captain Sisko's second USS Defiant would have been worthy of it.
Certainly Captain Sisko's second USS Defiant would have been worthy of it.
In fact, it was. Sort of. As always, budget concerns.
The original Defiant Class USS Defiant was a Prototype ship bearing the NX registry class, full registry number NX-74205. Production models of the Defiant Class ship bear the NCC registry class, like the NCC-75633 USS Sao Paulo. After the original is destroyed, Star Fleet essentially gifts it to Sisko with permission to rename it as the Defiant. With this, it also reuses the Registry Number of NX-74205.
[The Second] Defiant has conflicting registry numbers. The dedication plaque on the bridge gave the registry as NCC-75633, but the plaque also contained the original name Sao Paulo. External CGI images seen in "The Dogs of War" and "What You Leave Behind" repeated the old registry of NX-74205 as a result of the extensive use of stock footage from earlier episodes, including "The Search, Part I", "Sacrifice of Angels" and "Tears of the Prophets". The new footage in "What You Leave Behind" continued to use NX-74205 for consistency. In the Star Trek: Deep Space Nine Companion, Ron Moore stated that he intended for the ship to be designated the "Defiant-A" but it wasn't shown on screen because it would have been prohibitive to repaint and reshoot the model for one episode. Memory Alpha uses the commissioning year to differentiate this ship from its predecessor.
Since DS9 wasn't picked up for an eighth season,or hasn't had remastered shots, we never see it shown as an A variant, but in-universe logic would require it.
Another ship is the Galaxy Class NCC-1305-E USS Yamato, sister ship to the Enterprise-D. Though this was apparently a production mistake that made it on screen. The ship later gets many different registry numbers.
I don't have a canon reference, but as that's a practice some real-world navies indulge in, it's not unreasonable to assume it's just an acceptable practice for ships of some distinction in-universe:
It should be noted that European navies reuse pennant numbers, so ships of the same name may have the same pennant numbers (Wikipedia)
An example given is the HMS Galahad (L3005).
The reason for the Enterprise always having the same registration number with only an A, B,... added to it can be said in one word: Kirk.
To go into more details it was mentioned in the star trek films in passing. The original Enterprise 1701 had been destroyed in star trek 3. After kirk saved the earth again in star trek 4 at the end of it he got a new enterprise (or more a sister ship of her that got renamed). Because they wanted to honor him and the old enterprise they renamed the sistership to Enterprise and used the same registration number just with an A added to it at the end. With TNG it seems that they upheld this holding in honor of the Enterprise even almost 100 years later.
From Star Trek TNG: Technical Manual (bold by me):
The Galaxy-class Enterprise maintains Starfleet's tradition of honoring the original starship Enterprise. Like her predecessors, this ship bears the original Starfleet registry number of that illustrious first Enterprise, NCC-1701. In this case, the suffix "-D" indicates this is the fourth successor to the name and number. Few other ships in the Starfleet have been so recognized. So significant were the exploits of this original ship and its crew, that in 2277 the practice of having a separate insignia for each starship was abolished, and the Enterprise emblem was adopted as the official symbol for the entire Starfleet.
The practice to reuse registry numbers by adding a letter suffix to them was not a common practice, and is seems evident that having multiple reuses leading to letters "-D" or "-E" was exceptionally rare.
As reported, the reason for this practice was to honor a single starship that achieved exceptional results and whose importance in the history of the Federation was further recognized by adopting its insignia as the emblem of all Starfleet.
Like the first Enterprise, even her successor ships exploits were blessed with great achievements, further reinforcing the almost legendary status of ships sharing this name, making it so that "-D" Enterprise was bestowed the honorary title of flagship (but possibly other Enterprises as well).
In the end, it seems that with the Enterprise, standard specifications were loosened and tradition and prestige were the dominant factors in adopting what was an exceptional practice.
All in all, a registry should be unique and recognizable and unambiguously identify a given starship; for this purpose, if we take in example NCC-1701 and NCC-1701-D, they are two distinct and recognizable registries and uniquely identify a single starship, even if they share the numerical part.
