I don't think it should be it forever. A generation from now there will be a whole new part of the electorate who may well have different views. This should be it for at least 30 years, though, but the idea that one referendum should be the last word seems risky. The direction the UK takes might end up changing in a way that increases the clamour for independence in the future.
Rather like the EU, in fact. I'm hoping to stay in the EU (after some renegotations) but it's *** annoying that some sides of the argument respond to calls for a referendum with "we had one...". Yes. In the 1970's, long before I was born, and since then the EEC has changed markedly. Another referendum is probably appropriate. And so, too, it may be appropriate in Scotland once again, but not for a long while to come.