Thread: Simple, Add constraint

Working under the premise that "no question is too simple for this list"

Trying to add a constraint so that all phone numbers will be in a
'111-222-3333' format.  Tried a few combos, with below the latest effort.

ALTER TABLE "fortour"."mine" ADD CONSTRAINT mine_cnstr_2 CHECK (phone
LIKE '999s999s9999')

and .... LIKE '999-999-9999'

A constraint for length is in place without a problem.
Any help appreciated.

Thanks,
Mike Ellsworth

--- nhrcommu@rochester.rr.com wrote:
> Trying to add a constraint so that all phone numbers will be in a
> '111-222-3333' format.  Tried a few combos, with below the latest effort.
>
> ALTER TABLE "fortour"."mine" ADD CONSTRAINT mine_cnstr_2 CHECK (phone
> LIKE '999s999s9999')

in addition the the LIKE predicate, you can use regular expressions which are a more powerful tool
for pattern recognition.

   ALTER TABLE "fortour"."mine"
ADD CONSTRAINT mine_cnstr_2
         CHECK (phone ~ E'^[0-9]{3}-[0-9]{3}-[0-9]{4}$');

http://www.postgresql.org/docs/8.2/interactive/functions-matching.html#FUNCTIONS-POSIX-REGEXP

However, you can generalize this by using creating a DOMAIN called PhoneNumbers.  Notice the
example used for zipcodes in the following link:

http://www.postgresql.org/docs/8.2/interactive/sql-createdomain.html

Regards,
Richard Broersma Jr.

> > Trying to add a constraint so that all phone numbers will be in a
> > '111-222-3333' format.  Tried a few combos, with below the latest
> effort.
> >
> > ALTER TABLE "fortour"."mine" ADD CONSTRAINT mine_cnstr_2 CHECK
> (phone> LIKE '999s999s9999')
>
> in addition the the LIKE predicate, you can use regular expressions
> which are a more powerful tool
> for pattern recognition.
>
>   ALTER TABLE "fortour"."mine"
> ADD CONSTRAINT mine_cnstr_2
>         CHECK (phone ~ E'^[0-9]{3}-[0-9]{3}-[0-9]{4}$');
>
> http://www.postgresql.org/docs/8.2/interactive/functions-
> matching.html#FUNCTIONS-POSIX-REGEXP
>
> However, you can generalize this by using creating a DOMAIN called
> PhoneNumbers.  Notice the
> example used for zipcodes in the following link:
>
> http://www.postgresql.org/docs/8.2/interactive/sql-createdomain.html
>
> Regards,
> Richard Broersma Jr.

Thank you Richard.  I had read both before but without context for a
project.
Both are very helpful and the lightbulb has lit.

Thanks,
Mike Ellsworth