The past 2 games especially have shown how bad this mechanic is in my opinion. Civ5 implementation was no better than Civ6's implementation, it's worse imho. It's been a little too long to remember how well it worked in Civ4 and Civ3 (which I believe only UU's required resources in civ3?, I cannot remember it's been so long). Thoughts on why I feel units shouldn't require resources at the end of this post.
First idea is to completely eliminate the need for units to need resources. Any unit can be upgraded at any time provided they have the required technology. But of course we'd want to give an advantage to Civs that do have the resource associated with that unit. My first thought was to give them +5 on offense and defense if they have the previously required resource. Or would +7 be better? That may be too powerful.
My second thought was there's already a game mechanic for units that lose their required resources in that they stop healing. So we could make it so any unit can be upgraded, but if you lack the required resource, your units won't heal. This one may be a little too harsh, however. But perhaps more realistic. Think of German tank divisions late in WW2 who struggled to find enough oil to operate, and hence couldn't field full divisions. It's not like they went back to building knights when we took away their sources of oil.
My last thought is units should be forced to upgrade. Both AI and human units. This may be a little controversial. As a strategy game should be all about choice, and I'm hesitant to suggest forcing a player to do something. But in this case I feel like it will offer better and more realistic gameplay, and that outweighs strategy concerns. Now we wouldn't want to punish civs with good economies, they should have the outright advantage regardless. So I propose only 1 unit at a time automatically upgrades regardless of how much money you have stockpiled. Instead, gold per turn (rather than lump sum) is removed from your turn(ly) income until the unit is "paid for". Once that happens, a second unit is automatically upgraded until that unit is paid for, and so on. Civilizations with negative (or zero) per turn incomes cannot upgrade any units of course. The AI players seem too incompetent or poor to upgrade their armies, and some human players may purposely leave their army un-upgraded to save money on maintenance. Think about it, it's not like the U.S. was fielding divisions of musketmen in 1941 before WW2 broke out and suddenly decided to upgrade to infantry to fight the war. It's ridiculous. I'm tired of seeing outdated units on the game map. This has been a problem with this game from the start and needs to be fixed. One thing that would have to be balanced, is only removing a certain amount of gold per turn from the budget, like I said, civilizations with great economies should have the outright advantage. So maybe only -10 or -15 per turn until the unit is paid for (professional army card should still apply at the time the unit is upgraded).
I still don't believe it was lack of resources which doomed certain civilizations (Yes I know about Guns, Germs, and Steel). But you can't convince me that there was no Iron for the Native Americans. There certainly was Iron in the Eastern U.S. at least. I can't speak for South America, as I'm not familiar with that continent. It was lack of technology that doomed them, not necessarily lack of resources. The iron was always in those hills (of Pennsylvania), they just didn't know what to do with it. I feel if you have the required technology, then you should be able to upgrade. Of course I feel for my ideas above, technology costs should be upped by 15% at least in order to slow things down and space unit upgrades apart. As for gameplay reasons, yes we want the search for resources to be exciting and intense, but seeing these outdated units really breaks immersion. To me, better realistic immersion would be more beneficial than trying to promote fighting for resources. And as the way the warmongering system is right now, who wants to start a modern war just to get oil? I can often win the game without it. Though usually I just build a city out in the middle of nowhere to get it.
In theory, even nukes I feel should be able to be built without Uranium. Uranium 238 is not hard to come by, 20 countries mine the stuff. It's technology (and industrial capacity) that is needed to turn this into U235 or Pu239, in the form of breeder reactors, or centrifuges in the case of U235 (though this is mostly used for energy and not weapons). But since a nuke is a nuke in the game, and can't be made "less powerful", I would be okay by saying this is the only thing that should require a resource to be built since there's no easy way to give advantage to players who have Uranium.
I still want strategic resources to be useful. I just don't want to see knights in the modern age because a civilization lacks oil or Uranium (for modern armor). It's not like you see Slovenia's army using knights (I picked Slovenia since Wiki lists them at the bottom of oil production).
So give me 1 good reason why we should be seeing knights in the modern age for you strategic resource defenders.