-1

I am trying to create a check constraint to ensure that a stored varchar has atleast 1 letter and 1 number and am not sure exactly how to do this. Any help would be appreciated.

This is what I have so far:

CHECK (VALUE LIKE [a-z] AND LIKE [0-9])

will this work?

Thanks.

jacksontaylor
  • 13
  • 3
  • 4
    Can you, please, show us what you have tried? – Guneli Apr 28 '14 at 04:51
  • 1
    http://www.postgresql.org/docs/current/static/functions-matching.html –  Apr 28 '14 at 06:12
  • Please take a look at [the stack overflow tour](http://stackoverflow.com/tour) and [help center](http://stackoverflow.com/help) for advice on how to ask better questions to avoid downvotes, your questions being ignored, etc, and to help you get better answers. – Craig Ringer Apr 28 '14 at 06:14

2 Answers2

2

That syntax is invalid, so it can never work. At minimum you'd need to quote the pattern values in the LIKE expressions. They must also have wildcards so they aren't anchored; at the moment, your expression tries to say "value must be a single character that is a letter, and a single character that is a number". Which is impossible. Additionally, you can't use character classes ([...]) in LIKE.

What you want is to use a pair of simple regular expressions instead. This:

CHECK (thevalue ~ '[[:alpha:]]' AND thevalue ~ '\d')`

would do.

You can play some simple games with the expression to force this into a single regular expression, but I don't think there's much point, it'll just be harder to read and uglier.

All that said, I'm worried that you might be attempting to validate password strength. If you are doing that then this is absolutely the wrong way to go about it. Do not use a CHECK constraint - you won't be able to introduce stronger requirements later until all existing rows pass the constraint. Do the check as input validation in your application instead, and use proper password strength checking tools - which already exist - instead of rolling your own.

Craig Ringer
  • 307,061
  • 76
  • 688
  • 778
-1

Take a look at the manual on constraints and the w3resource page on the PostgreSQL CHECK constraint.

Craig Ringer
  • 307,061
  • 76
  • 688
  • 778
Hardik Vinzava
  • 968
  • 10
  • 22
  • Link-only answers are generally not preferred on Stack Overflow - links change and go away, and they don't tell the reader anything much when they're browsing a list of answers. Try to provide a little explanation and a link for more detail. – Craig Ringer Apr 28 '14 at 06:12
  • @CraigRinger thanks for your suggestion. My answer pointed to the question before editing the question where no code shared by user. So i just pointed user to the related topic ! – Hardik Vinzava Apr 28 '14 at 06:16