That's a very good point - it's the basic reason that Obama can't 'end the drug war with a stroke of his pen' as some people seem to imagine, because treaties ratified by the US have almost constitutional standing in legal terms, and need to be formally derogated rather than legislated away. While the US could certainly do so without suffering meaningful sanctions or major geopolitical harm over the long term, indifference to treaty obligations is often the hallmark of an insular political viewpoint - but then such people also tend to be uninterested in the ramifications for international trade and cross-border institutions in the first place.
That said, the SCND dates back to 1961 which was 53 years ago, and those countries which have openly stepped back from full compliance with it such as Portugal and Uruguay don't seem to have incurred any disadvantage or sanctions worth talking about, though you might argue that they had little or no political capital to lose in the first place. I do think there's a global shift towards looking at drug abuse and addiction as public health issues rather than moral vices, so while there's pressure form some quarters to maintain or strengthen anti-drug enforcement there's also pressure to adopt more utilitarian policies focused on harm minimization and demand management.
I think the convention will not be explicitly derogated any time soon, but will rather be allowed to become a dead letter through diminished contributions of fiscal and political capital for enforcement, while being nominally and loudly upheld by more authoritarian regimes as both a blunt instrument in trade negotiations and as a proxy for the rule of law.