Обсуждение: 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