Thread: constraining chars for all cols of a table

Hi:

I have to constrain the chars used for table columns.  For example...

   create table foo (col1 text, col2 text, col3 text);

... where

    col1 has to be all uppercase, nothing but [A-Z]

    col2 has to be all lowercase [a-z] plus [0-9] is also allowed

    col3 can be mixed case plus [0-9] and sqr brackets (but nothing else).

I could put a check constraint on each/every table column, but that seems complicated and potentially slow.

I could do this check using the existing insert and update before triggers, but then I'd have to loop through all the columns and decide one by one how to

check them.  Again, slow and complicated.

Is there a better way?  Whatever I do, I'd have to be able to capture violations to provide informative feedback  to the users through the perl script that'll actually be doing the insert/update.

Thanks in Advance !

Restrict access to the table (for inserts) to a function that does the verification and then executes the insert in addition to any kind of logging and “RAISE”ing you need.

If you need to validate existing data I’d probably just do some one-time verifications and updates where required. 

A column “CHECK” constraint, however, seems like it should work just find if you use a regular expression – and I cannot imagine it would be that performance limiting.

Without a more specific model in mind choosing between different approaches is difficult.

David J.

Hi:

I have to constrain the chars used for table columns.  For example...

   create table foo (col1 text, col2 text, col3 text);

... where

    col1 has to be all uppercase, nothing but [A-Z]

    col2 has to be all lowercase [a-z] plus [0-9] is also allowed

    col3 can be mixed case plus [0-9] and sqr brackets (but nothing else).

I could put a check constraint on each/every table column, but that seems complicated and potentially slow.

I could do this check using the existing insert and update before triggers, but then I'd have to loop through all the columns and decide one by one how to

check them.  Again, slow and complicated.

Is there a better way?  Whatever I do, I'd have to be able to capture violations to provide informative feedback  to the users through the perl script that'll actually be doing the insert/update.

Thanks in Advance !

>>Restrict access to the table (for inserts) to a function that does the verification and then executes the insert in addition to any kind of logging and >>“RAISE”ing you need.

Wouldn't that be akin to doing the checking in the insert and update before triggers?  That's certainly possible, but I fear the performance hit if I have to do a metadata query to get all the column names, then check them all one by one against a regexp that each maps to.  This should be no problem for single record inserts by users.  But some of these insert triggers cascade the creation of hundreds or thousands of additional records recursively. If the added overhead is a hald second per for what becomes a 1000 record creation, that's over 8 minutes !

I was looking at enum, and something like that would work if I could replace the hardcoded list of values with something like a regexp.

Still looking :-)

Restrict access to the table (for inserts) to a function that does the verification and then executes the insert in addition to any kind of logging and “RAISE”ing you need.

If you need to validate existing data I’d probably just do some one-time verifications and updates where required. 

A column “CHECK” constraint, however, seems like it should work just find if you use a regular expression – and I cannot imagine it would be that performance limiting.

Without a more specific model in mind choosing between different approaches is difficult.

David J.

Hi:

I have to constrain the chars used for table columns.  For example...

   create table foo (col1 text, col2 text, col3 text);

... where

    col1 has to be all uppercase, nothing but [A-Z]

    col2 has to be all lowercase [a-z] plus [0-9] is also allowed

    col3 can be mixed case plus [0-9] and sqr brackets (but nothing else).

I could put a check constraint on each/every table column, but that seems complicated and potentially slow.

I could do this check using the existing insert and update before triggers, but then I'd have to loop through all the columns and decide one by one how to

check them.  Again, slow and complicated.

Is there a better way?  Whatever I do, I'd have to be able to capture violations to provide informative feedback  to the users through the perl script that'll actually be doing the insert/update.

Thanks in Advance !

I don’t fully follow the issue or usage pattern.  It may not be perfect database design but you should be most concerned with user generated data.  If you are cascading within the database (or application) system and you have this kind of extreme 1-input/1000-output relationship then you should TEST and VERIFY that the “cascading” code generates valid data (given valid input) and restrict your focus to validating the original input.

>>Restrict access to the table (for inserts) to a function that does the verification and then executes the insert in addition to any kind of logging and >>“RAISE”ing you need.

Wouldn't that be akin to doing the checking in the insert and update before triggers?  That's certainly possible, but I fear the performance hit if I have to do a metadata query to get all the column names, then check them all one by one against a regexp that each maps to.  This should be no problem for single record inserts by users.  But some of these insert triggers cascade the creation of hundreds or thousands of additional records recursively. If the added overhead is a hald second per for what becomes a 1000 record creation, that's over 8 minutes !

I was looking at enum, and something like that would work if I could replace the hardcoded list of values with something like a regexp.

Still looking :-)

Restrict access to the table (for inserts) to a function that does the verification and then executes the insert in addition to any kind of logging and “RAISE”ing you need.

If you need to validate existing data I’d probably just do some one-time verifications and updates where required. 

A column “CHECK” constraint, however, seems like it should work just find if you use a regular expression – and I cannot imagine it would be that performance limiting.

Without a more specific model in mind choosing between different approaches is difficult.

David J.

Hi:

I have to constrain the chars used for table columns.  For example...

   create table foo (col1 text, col2 text, col3 text);

... where

    col1 has to be all uppercase, nothing but [A-Z]

    col2 has to be all lowercase [a-z] plus [0-9] is also allowed

    col3 can be mixed case plus [0-9] and sqr brackets (but nothing else).

I could put a check constraint on each/every table column, but that seems complicated and potentially slow.

I could do this check using the existing insert and update before triggers, but then I'd have to loop through all the columns and decide one by one how to

check them.  Again, slow and complicated.

Is there a better way?  Whatever I do, I'd have to be able to capture violations to provide informative feedback  to the users through the perl script that'll actually be doing the insert/update.

Thanks in Advance !

Yes, I was doing that in the app that inserts/updates the records.  But someone coming at this via direct sql would bypass the check in the app.  :-(

I don’t fully follow the issue or usage pattern.  It may not be perfect database design but you should be most concerned with user generated data.  If you are cascading within the database (or application) system and you have this kind of extreme 1-input/1000-output relationship then you should TEST and VERIFY that the “cascading” code generates valid data (given valid input) and restrict your focus to validating the original input.

>>Restrict access to the table (for inserts) to a function that does the verification and then executes the insert in addition to any kind of logging and >>“RAISE”ing you need.

Wouldn't that be akin to doing the checking in the insert and update before triggers?  That's certainly possible, but I fear the performance hit if I have to do a metadata query to get all the column names, then check them all one by one against a regexp that each maps to.  This should be no problem for single record inserts by users.  But some of these insert triggers cascade the creation of hundreds or thousands of additional records recursively. If the added overhead is a hald second per for what becomes a 1000 record creation, that's over 8 minutes !

I was looking at enum, and something like that would work if I could replace the hardcoded list of values with something like a regexp.

Still looking :-)

Restrict access to the table (for inserts) to a function that does the verification and then executes the insert in addition to any kind of logging and “RAISE”ing you need.

If you need to validate existing data I’d probably just do some one-time verifications and updates where required. 

A column “CHECK” constraint, however, seems like it should work just find if you use a regular expression – and I cannot imagine it would be that performance limiting.

Without a more specific model in mind choosing between different approaches is difficult.

David J.

Hi:

I have to constrain the chars used for table columns.  For example...

   create table foo (col1 text, col2 text, col3 text);

... where

    col1 has to be all uppercase, nothing but [A-Z]

    col2 has to be all lowercase [a-z] plus [0-9] is also allowed

    col3 can be mixed case plus [0-9] and sqr brackets (but nothing else).

I could put a check constraint on each/every table column, but that seems complicated and potentially slow.

I could do this check using the existing insert and update before triggers, but then I'd have to loop through all the columns and decide one by one how to

check them.  Again, slow and complicated.

Is there a better way?  Whatever I do, I'd have to be able to capture violations to provide informative feedback  to the users through the perl script that'll actually be doing the insert/update.

Thanks in Advance !

Hence my original comment.  If you move the logic that was in the application into the database via a function, and restrict direct inserts and updates other via that (or supporting) functions, you force direct SQL users to use those functions – and thus your validation logic – to get the data into those tables.

Whether the application logic CAN be represented in a SQL function is another matter but it is at least something to consider.

Yes, I was doing that in the app that inserts/updates the records.  But someone coming at this via direct sql would bypass the check in the app.  :-(

I don’t fully follow the issue or usage pattern.  It may not be perfect database design but you should be most concerned with user generated data.  If you are cascading within the database (or application) system and you have this kind of extreme 1-input/1000-output relationship then you should TEST and VERIFY that the “cascading” code generates valid data (given valid input) and restrict your focus to validating the original input.

>>Restrict access to the table (for inserts) to a function that does the verification and then executes the insert in addition to any kind of logging and >>“RAISE”ing you need.

Wouldn't that be akin to doing the checking in the insert and update before triggers?  That's certainly possible, but I fear the performance hit if I have to do a metadata query to get all the column names, then check them all one by one against a regexp that each maps to.  This should be no problem for single record inserts by users.  But some of these insert triggers cascade the creation of hundreds or thousands of additional records recursively. If the added overhead is a hald second per for what becomes a 1000 record creation, that's over 8 minutes !

I was looking at enum, and something like that would work if I could replace the hardcoded list of values with something like a regexp.

Still looking :-)

Restrict access to the table (for inserts) to a function that does the verification and then executes the insert in addition to any kind of logging and “RAISE”ing you need.

If you need to validate existing data I’d probably just do some one-time verifications and updates where required. 

A column “CHECK” constraint, however, seems like it should work just find if you use a regular expression – and I cannot imagine it would be that performance limiting.

Without a more specific model in mind choosing between different approaches is difficult.

David J.

Hi:

I have to constrain the chars used for table columns.  For example...

   create table foo (col1 text, col2 text, col3 text);

... where

    col1 has to be all uppercase, nothing but [A-Z]

    col2 has to be all lowercase [a-z] plus [0-9] is also allowed

    col3 can be mixed case plus [0-9] and sqr brackets (but nothing else).

I could put a check constraint on each/every table column, but that seems complicated and potentially slow.

I could do this check using the existing insert and update before triggers, but then I'd have to loop through all the columns and decide one by one how to

check them.  Again, slow and complicated.

Is there a better way?  Whatever I do, I'd have to be able to capture violations to provide informative feedback  to the users through the perl script that'll actually be doing the insert/update.

Thanks in Advance !