If a contradiction is discovered in set theory, either it will be fixed by a change to the axioms, or another set of foundational axioms altogether will become the standard foundation of mathematics. Perhaps the canonical example of this is naive set theory itself - paradoxes were discovered, but set theory was interesting enough that mathematicians looked for alternate definitions that did not contain the paradoxes, and they succeeded.
There are two things to note here. First, axiomatization was a useful tool in distinguishing various types of set theory and defining the ones that did not contain the (known) paradoxes. Second, the full formal treatment of these set theories came relatively late to mathematics, as did widespread use of formal, axiomatic methods in general. Rigorous axiomatic formalisms are very useful mathematical tools, but mathematics got along without them for a long time (not even Euclid counts, by modern standards) and can do so again if necessary. It is unlikely that this will happen, not because axiomatics can be proved to always work or because mathematics cannot get along without it, but because it is too useful a tool to abandon easily.
This was really Godel's point: mathematics is not identical with formalism. They stand and fall separately. This is not to say that mathematics could never collapse for any reason, only that it would take a lot more than finding a paradox at the center of ZFC to make it happen.
That's significant because another great mathematician David Hilbert challenged mathematicians to come up with a complete and consistent set of axioms for all of mathematics. This was a great hope at one time that was shattered by Godel. Most people don't really talk about this old program anymore (except as a historical curiosity) because there is utterly no hope in it at all. The other implications that people try to draw from Godel's work is probably a consequence of the fact that his work sounds like it says so much more than it actually does when it's translated into normal English (and out of math-speak).
So strictly speaking, the theorem doesn't rule out consistency proofs, but it does rule out proving the consistency of complex systems by simpler systems, meaning that the consistency of the consistency proofs can't be trusted any more than the consistency of the original system!
If you are not a mathematician chances are you've never encountered a system based on axioms without "Axiom of Choice" (for systems where it is applicable at all).
You are in a good company thinking that infinity cannot be ever treated as a number http://scienceblogs.com/goodmath/2008/10/infinity_is_not_a_n...
Also, there are the cardinal numbers, for which the interesting ones consist entirely of infinity.
Are there negative numbers? Zero? Imaginary unit?
There were times when people even didn't know a concept of zero.
A concept of infinity is really useful and it is used almost everywhere.
Hopefully I am citing it right, as I am too lazy to look it up. But I think that is more of a ticking bomb sitting in the guts of mathematics. It could happen that one of these days the whole building of mathematics collapses because a contradiction in the axioms emerges. Apparently the common way to deal with it is to just deem it very unlikely since things went fine for centuries, and there is not much that can be done about it anyway.
Must finally read Gödel's proofs again, have been meaning to do so for ages :-/