#1
  1. Me likey breadsticks...
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jan 2003
    Location
    Los Angeles
    Posts
    1,189
    Rep Power
    18

    Check Constraints Question


    I was just wondering what people's opinions were regarding check constraints on data vs. a foreign key reference to a separate table by id to a description.

    For smaller type sets like sex, I could see how it would be simpler to use a check constraint vs. creating a separate table just to store 2 rows of male and female, however how about when your data range extends beyond this.

    For example let's say I have table that tracks incidents to an ER room and I want a field to keep track of the mode of arrival (possible ones are: carried, walked in, wheelchair, ambulance, and, say, a couple more) . Should I just have a check constraint on my incident field with those possible values, or should I have a foreign key field that links to a mode of arrival table with a mode of arrival ID and a mode of arrival description?

    I can see that a separate table may have advantages because if I wanted to I can add more possible modes of arrival without changing the constraint, and with cascading deletes I could also get rid of all incidents of a certain type by deleting from the mode of arrival table. However if I have several fields like this, what about the performance penalties of multiple joins to retrieve the descriptions vs, just getting the valid values within the check constraint?

    Are check constraints just better for handling value ranges, instead of actual values? Would a decent generalization be that if you're using 'IN' in your check constraint statement then you might be better off using a separate table with a foreign key?

    Any constructive comments would be greatly appreciated.

    -b
    PostgreSQL, it's what's for dinner...
  2. #2
  3. No Profile Picture
    Apprentice Deity
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 1999
    Location
    Niagara Falls (On the wrong side of the gorge)
    Posts
    3,237
    Rep Power
    19
    I wouldn't use an ID field, just a table with one field that is the descriptor. The only time I would use an incremented ID field as the ID is if multiple fields are needed to achieve uniqueness or if the one field would be so large (say more than a few dozen characters in length) as to make using it a resource problem.
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2003
    Posts
    373
    Rep Power
    12
    can you define a new datatype with domain or just have a domain on that field? We learned about those type of table constraints and they're in that Ramakrishnan & Gehrke book, but I don'[t know if they are implemented in postgres
  6. #4
  7. Me likey breadsticks...
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jan 2003
    Location
    Los Angeles
    Posts
    1,189
    Rep Power
    18
    Domains are supported (however not fully) as they don't support check constraints yet.

    My question really isn't so much about syntax, more what is the established/preferred approach when dealing this type of thing.

    rod k, are there times when you'd just use a check constraint vs. the separate table? Also, initially, I favored the ID field thinking an int field would be better utilized better for indexes (not having to worry about case sensitivity, etc) however since it is a foreign key those cases wouldn't apply. And then I thought that there would be a savings in space by story an int as opposed to text, however since space is relatively cheap nowadays, is this offset by the fact that if I don't use an ID field and just store the foreign keyed text I won't need extra processing for the join to get the description? Or are there other reasons as well?

    Thanks for both your input.

    -b
    PostgreSQL, it's what's for dinner...
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2003
    Posts
    373
    Rep Power
    12
    Although using a CHECK constraint would solve the general problem, I can see that it would be an issue if you if improperly formatted strings; however, if you are doing access through some outside entity, you could probably take care of it at that stage for data uniformity's sake. Using the foreign key constraints as you outlined in your original post is the closest to the general paradigm of good RDBMS data normalization. If this is the most error-free method of thought for you as the programmer, then this seems like the most straightforward solution. Having multiple tables for this sort of data integrity is just a fact of life without using more breakable constructs like CHECK or defining a DOMAIN. you could also rely on the outside logic of the programs accessing the database to take care of this integrity, but that seems like a recipe for disaster.

    in what situations would you NOT want to use a CHECK constraint?
  10. #6
  11. No Profile Picture
    Apprentice Deity
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 1999
    Location
    Niagara Falls (On the wrong side of the gorge)
    Posts
    3,237
    Rep Power
    19
    are there times when you'd just use a check constraint vs. the separate table?
    Let me put it this way. Too often in the past I'd thought "I don't need a seperate table, just a check constraint because my list won't grow." only to have it bite me in the a$$.

    The other issue is with applications. I have standard objects for creating lists from queries, so adding another table isn't a big deal, but hard coding options is. (Yes I realize I could get the list of check constraint options by querying the constraint from the system tables, but UGGGH!)

    however since space is relatively cheap nowadays, is this offset by the fact that if I don't use an ID field and just store the foreign keyed text I won't need extra processing for the join to get the description?
    In my mind, yes, usually.

    Domains are supported (however not fully) as they don't support check constraints yet
    I'm waiting rather impatiently for 7.4 to become stable for this feature alone.
  12. #7
  13. No Profile Picture
    Gödelian monster
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jul 1999
    Location
    Central Florida, USA
    Posts
    2,307
    Rep Power
    62
    Originally posted by rod k
    I'm waiting rather impatiently for 7.4 to become stable for this feature alone.
    You and me both, brother .

    Yes, I agree with Rod: it's usually better to think of things like lists as requiring an external validation table, which is the sort of thing the relational model was made for. I tend to use CHECK constraints more for scalar limitations, such as preventing a integer from exceeding a certain size, or from going negative, or for such things as regex constraints on usernames and URL paths.

    Also, table-level CHECK constraints are great for such things as preventing a value in one column from exceeding a value in another, etc...

    KEEP LISTS IN TABLES

    'nuff said
    The real n-tier system:

    FreeBSD -> PostgreSQL -> [any_language] -> Apache -> Mozilla/XUL

    Amazon wishlist -- rycamor (at) gmail.com
  14. #8
  15. Me likey breadsticks...
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jan 2003
    Location
    Los Angeles
    Posts
    1,189
    Rep Power
    18
    Thanks all for the helpful replies, things seem crystal clear for me now.

    -b
    PostgreSQL, it's what's for dinner...

IMN logo majestic logo threadwatch logo seochat tools logo