Justifying constraints

[KittyKate]

I can remember being lectured on the importance of using varchar(#) in school, but I can't remember the reasoning any more. I am now working with someone who has never had any formal training in databases (and very little informal), and I need to convince him that it is worth it to set the upper limit on varchars. I've taken the fact for granted for so long, I really can't think of *strong* reasons to do it.

Help! Why do we set types the way we do?? I need reasons for using varchars over chars (outside of the obvious blank spaces, because for some reason that doesn't seem good enough), reasons for setting lengths on varchars, and why you should use text over an unlimitted varchar.

[btherl]

Constraints are not always good.  If your lecturers said they were ALWAYS good, they were wrong :)

I would say that if you can't think of any reason to use a constraint, then you shouldn't be using one.  If you can think of a reason to use one, then you should be using one.  Usually you set a constraint as an aid to debugging.  The assumption is that your code is not perfect, and constraints (including types, lengths, and other more complex constraints) will help you pick up bugs before they grow into horrible monsters that eat you alive.

There are actually many hidden constraints, such as the character set of the database, which you are already using.  And that constraint is useful if the database character set matches the display character set.  Calling a column a numeric rather than a varchar is also a constraint, limiting the column to holding numeric values only.  It makes a lot of sense if you want to store dollar+cent values.  It ensures that you don't accidentally store something else there.

But if you have no reason to constrain a value, then don't constrain it.  Have you seen how much trouble airlines have because the ticket name field can't fit long names?  They have to kludge around it.  But in that case, it does make some kind of sense, because the name MUST be short enough to fit on the ticket.

On some databases, varchar length constraints may have helped efficiency.  But on postgresql, it doesn't help.  Varchars with lengths are stored identically to unbounded varchars.