Thread: Informing end-user of check constraint rules
I have not used column check constraints before, but I'd like to start using then and so would I'll like to know if there is a direct way to provide feedback to the end user about data validation rules expressed in column check constraints? For instance, say that I wanted to use a RE to check e-mail address format validity and then the data entry clerk typed in invalid data. My understanding is that when the check constraint returns FALSE, the row will not insert, and an ExecAppend: rejected due to CHECK constraint "table_column " exception is raised. That at least tells the column (albeit in language that would scare the computer-phobe), but I like the exception message to tell the end user what the format is supposed to be. Is my only option to have the end-user application (as opposed to the database) inform the end-user what the correct data format is? If THAT is so, then it seems I might as well also perform the data formatting validation in the application, too, so that at least they'ld both be in the same place and not two separate places. What I'd like is to be able to specify some kind of error message telling the user what the correct format should be, and since the proper format is specified in the database, i.e., in the check constraint, it seems that the proper place to raise an exception providing the remedial instructions would also be in the database. ~Berend Tober
Am Sonntag, 22. Juni 2003 14:45 schrieb btober@seaworthysys.com:
> I have not used column check constraints before, but I'd like to start
> using then and so would I'll like to know if there is a direct way to
> provide feedback to the end user about data validation rules expressed in
> column check constraints?
>
> For instance, say that I wanted to use a RE to check e-mail address
> format validity and then the data entry clerk typed in invalid data. My
> understanding is that when the check constraint returns FALSE, the row
> will not insert, and an ExecAppend: rejected due to CHECK constraint
> "table_column " exception is raised. That at least tells the column
> (albeit in language that would scare the computer-phobe), but I like the
> exception message to tell the end user what the format is supposed to be.
> Is my only option to have the end-user application (as opposed to the
> database) inform the end-user what the correct data format is? If THAT is
> so, then it seems I might as well also perform the data formatting
> validation in the application, too, so that at least they'ld both be in
> the same place and not two separate places.
>
> What I'd like is to be able to specify some kind of error message telling
> the user what the correct format should be, and since the proper format
> is specified in the database, i.e., in the check constraint, it seems
> that the proper place to raise an exception providing the remedial
> instructions would also be in the database.
you can use a trigger on insert and write your own error handling function
like below. then you have everything at one place. I think postgres should
have better ways to report errors but i am not a database guru and dont know
how other databases do their error handling.
Maybe its better to have some kind of "middleware" to keep the business logic
and use the database just to store data... i thought about it a lot and tried
to find relevant informations about how to model data/businesslogic/frontend
in a convienient way...
here is an example to check different columns and return an explanation of on
or more errors. of course your frontend has to parse this errormsg for the
relevant part shown to the user. its just copied but a little bit modified
code from a working example. but this code below isn't tested and
errormessages are in german.
what i like most is not having good error message but you can show all errors
at once.
kind regards
janning
CREATE TRIGGER tg_user BEFORE INSERT OR UPDATE ON USER FOR EACH ROW EXECUTE
PROCEDURE tg_user_col_check();
CREATE FUNCTION tg_user_col_check () RETURNS TRIGGER AS ' DECLARE var_errmsg text := ''TIPPER''; var_error
boolean; rec_any RECORD; var_count int4; var_maxmitgl int4 := 1000; --------------- -- email --
--------------- NEW.email := btrim(NEW.email);
IF NEW.email !~ ''^[A-Za-z0-9.@_-]+$'' THEN var_error := ''true''; var_errmsg := var_errmsg || ''#name:''
|| ''Die E-Mail Adresse darf nur aus Buchstaben, Zahlen und einigen
Sonderzeichen ("_", "-", "@", ".") bestehen. ''; END IF;
IF length(NEW.name) < 3 THEN var_error := ''true''; var_errmsg := var_errmsg || ''#name:'' || ''Der
Benutzernamemuss mindestens drei Zeichen lang sein"; END IF;
IF length(NEW.email) > 50 THEN var_error := ''true''; var_errmsg := var_errmsg || ''#email:'' || ''Die
E-MailAdresse darf nicht länger als 50 Buchstaben sein''; END IF;
IF var_error THEN RAISE EXCEPTION ''%'', var_errmsg; END IF; RETURN NEW; END;
' language 'plpgsql';
> Am Sonntag, 22. Juni 2003 14:45 schrieb btober@seaworthysys.com:
>> I have not used column check constraints before, but I'd like to
>> start using then and so would I'll like to know if there is a direct
>> way to provide feedback to the end user about data validation rules
>> expressed in column check constraints?
>>
> you can use a trigger on insert and write your own error handling
> function like below. then you have everything at one place. I think
> postgres should have better ways to report errors but i am not a
> database guru and dont know how other databases do their error
> handling.
>
I came up with what I think is a pretty cool implementation idea, as
follows.
-- 1) Define a table as
CREATE TABLE regular_expression ( description character varying(48) NOT NULL, regular_expression character
varying(128), user_message text
) WITHOUT OIDS;
-- with sample data:
INSERT INTO regular_expression VALUES ('Social Security Number',
'^\\d{3}-\\d{2}-\\d{4}$', 'Social Security Number must consist of the
pattern: nnn-nn-nnnn, where "n" is a digit.');
INSERT INTO regular_expression VALUES ('US Telephone Number',
'^[2-9]\\d{2}-\\d{3}-\\d{4}', 'US Telephone numbers must consist of the
pattern aaa-eee-nnnn, optionally followed by extra extension number,
where aaa is the three-digit area code, eee is the three digit exchange
code, and nnnn is the four digit number.');
INSERT INTO regular_expression VALUES ('Internet E-Mail Address',
'[a-z0-9_]+([\\\\-\\\\.][a-z0-9_]+)*@[a-z0-9_]+([\\\\-\\\\.][a-z0-9_]+)+',
'Internet E-Mail Addresses are typically of the form nnnnn@dddddd.ttt,
where, "nnnnn" is the user name, "dddddd" is the Internet domain name,
and "ttt" is the three character top-level domain name.');
INSERT INTO regular_expression VALUES ('US ZIP Code',
'^\\d{5}-\\d{4}|\\d{5}$', 'US Postal ZIP Codes are of the form nnnnn or
nnnnn-nnnn, where "n" is any digit.');
COMMENT ON TABLE regular_expression IS 'This table defines regular
expressions used in the application.';
-- 2) Define the following function to be used as a generic CHECK
constraint:
CREATE FUNCTION public.check_pattern(varchar, varchar) RETURNS bool AS '
DECLARE l_value ALIAS FOR $1; l_pattern ALIAS FOR $2; l_row RECORD;
BEGIN IF l_value IS NOT NULL THEN IF EXISTS(SELECT 1 FROM public.regular_expression WHERE
UPPER(description) = UPPER(l_pattern)) THEN SELECT INTO l_row regular_expression, user_message FROM
public.regular_expression WHERE UPPER(description) =
UPPER(l_pattern); IF NOT (l_value ~ l_row.regular_expression) THEN RAISE EXCEPTION ''Invalid %. %'',
l_pattern,l_row.user_message; END IF; END IF; END IF; RETURN TRUE;
END;
' LANGUAGE 'plpgsql' VOLATILE;
-- 3) Define any check constraint you want similar to:
CREATE TABLE person ( e_mail_address varchar(128), social_security_no varchar(11),
-- [...other column defs...] CONSTRAINT person_e_mail_address CHECK (check_pattern(e_mail_address,
'Internet E-Mail Address')), CONSTRAINT person_social_security_no CHECK
(check_pattern(social_security_no, 'Social Security Number'))
) WITHOUT OIDS;
The only hitch I've run into so far is that when I want to do an ALTER
TABLE to ADD a CHECK CONSTRAINT this way when there is existing data, I
need to temporarily CREATE OR REPLACE the check_pattern function with the
RAISE EXCEPTION line commented out because for some reason that
particular exception gets raised in the process of trying to add the
constraint, and so the ADD CONSTRAINT command fails. After the constraint
is successfully added with the function's RAISE EXCEPTION call commented
out, then I re-CREATE OR REPLACE the check_pattern function with the
RAISE EXCEPTION line restored.
Seems to work great in preliminary testing.
~Berend Tober