Firstly, I was a SAHM when DH and I bought this house. I had been working full time before DS came along, I gave up work and we rented to move to a better area - we put the equity from that sale down as a deposit on this house.
I'm on both the mortgage and the deeds despite no income at the time (I work part time now and have my own small craft business).
Secondly, if you both agree a mortgage is the way to be able to move up and out, I agree about using your Right To Buy. It's been explained in depth up thread but to do a quick side by side comparison. And this is just a very rough thing to give you an idea.
a) You put £10K down on a £200K house and borrow £190K in a mortgage. Which means borrowing 4.75 times his salary if he's on £40K. That, on Barclays, works out between £990 and £1000 per month. Again, on Barclays, looking for a 95% repayment mortgage only gives 2 to choose from. Bear that in mind as you read on.
b) You find out that your current council home is valued at £150K. You've been in your property as a tenant for five years (you get more discount the longer you've been there but let's assume 5 years to start with). So that's a 35% discount in most places (but you'd need to check with the council and work out how many years discount you can get). But 35% discount is is £52,500. Which means your mortgage is £97,500. Which, if he's on £40K, is just under 2.5 times his salary. A very quick and dirty calculation on Barclays makes that just under £500 per month or thereabouts in mortgage repayments. And the number of different repayment mortgages Barclays could offer you? 18
Five years pass and you're now out of the sliding scale of paying any of the discount back if you decide to sell. Let's assume house prices have stayed where they are just for the sake of argument. So you now want that £200K house in a nicer area. You sell for £150K which is what your house was originally worth. You don't have to pay back the discount of £52,500. You can use that as the deposit on the £200K house. Which means you now need a mortgage of £147,500. And that means him borrowing around 3.75 times his salary. And, again putting that into Barclays mortgage calculator on line shows a monthly payment of £670 - £750. And, again, you'd be offered from 18 different repayment mortgages with them.
And, of course, Barclays aren't the only mortgage lenders - I just chose them as they came up with the first on line calculator when I searched!
Of course, you also have to factor in legal fees, stamp duty and all the rest but that would be the same if you bought the £200K house now or later.
And, as others have said, you can always go on a council exchange list to see if you can do a house swap. There are always people needing to move for all sorts of reasons. My parents swapped twice when I was growing up and mum swapped two more times when my dad died as she didn't need such a large house and then wanted to move closer to us.
I would check with the council what discounts they offer. Do a bit of general playing with figures on line to see what you could borrow. Find out what that mortgage amount would cost. Find out what fees you have to pay on top, like solicitors, stamp duty etc., That will give you a figure to look at which will either make you go 'oh, doable!' or 'oh God, no life!' It'll help you and your DH narrow it all down a bit.
I'm not getting into the relationship between you and DH. I do wonder, though, if when he said about getting a mortgage on your council house then moving you all into rented, he was thinking you could get a tenant. Which, for example, in our current house, we could. But in RTB until the discounted period (usually five years) is over, I don't think you can. So you'd have to stump up mortgage on an empty house for five years and rent on the one you're living in. He may not have even looked into it enough to know that yet.