Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

Dear Monks,

Please excuse this off topic post but I find that perlmonks.com is the only place where I can get useful and timely feedback on software issues. I am questioning the wisdom of using a foreign key constraint to enforce check constraint like functionality.

I have several tables that I use to "decode" integer values to more readable strings.

Example: I have one table called department which is defined like so.
ID            NUMBER PRIMARY KEY
DEPT_CODE     VARCHAR2(5) 
...

[download]
And I add the foreign key with the alter table command. 
ALTER TABLE DEPARTMENT ADD CONSTRAINT DEPT_NUM_CK_FK
FOREIGN KEY ( DEPT_CODE ) REFERENCES DEPARTMENT_CODES.CODE;

[download]
The DEPARTMENT_CODES table has values like this: 
SEQ   CODE    NAME 
---   -----   -----------------------------
  1   H23R    Human Resources 
  2   1126    R & D
...

[download]
This way when someone try's and enters a code that is not in DEPARTMENT_CODES table they will get a integrity constraint violate.

I was just wondering if any monks have used this type of approach and if using check constraints maybe better. We have about 2000 values in the DEPARTMENT_CODE table.
Thanks for you time :)

Replies are listed 'Best First'.
Re: (OT) database design question
by dragonchild (Archbishop) on Oct 20, 2003 at 17:07 UTC
    Foreign keys are the way to describe relationships between two tables. They are simple, optimized within the engine, and are self-commenting. Check constraints should be limited to when you simply cannot do it any other way. (And, in most of those cases, re-normalizing your structure eliminates the need for check constraints.)

    ------
    We are the carpenters and bricklayers of the Information Age.

    The idea is a little like C++ templates, except not quite so brain-meltingly complicated. -- TheDamian, Exegesis 6

    ... strings and arrays will suffice. As they are easily available as native data types in any sane language, ... - blokhead, speaking on evolutionary algorithms

    Please remember that I'm crufty and crochety. All opinions are purely mine and all code is untested, unless otherwise specified.

[reply]
Re: (OT) database design question
by Plankton (Vicar) on Oct 20, 2003 at 18:58 UTC
    What would happen if your company decided to dump its R & D department? If you where to update your DEPARTMENT_CODE table to reflect this new organzition you would have records in the DEPARTMENT table that would not have parent keys! Or you could keep the R & D department's code but you won't be able to catch data entry errors! Both are bad. I think you should use triggers or something else to implement business rule to enforce data validation.

    Plankton: 1% Evil, 99% Hot Gas.
[reply]
      Well if departments are added and removed I would leave them in there and when you build your data entry form check if the "active_department" flag is set before listing the acceptable departments. That way you always have parent keys AND data entry errors while using external keys. Triggers add no advantage here.


      -Waswas
[reply]
        Maybe if I explain what I am trying to do more. We get two flat files from another group. This group is difficult to deal with and lacks any techical people and won't add any flags to the data. One file contains the DEPARTMENT table data and the DEPARTMENT_CODE table contains the current valid DEPARTMENT_CODE values, so the actual data entry is done somewhere else and I am suppose to come up with a way to automate the loading of these flat files into the there corresponding tables. I am not allowed to delete any records from the DEPARTMENT table and I must replace the DEPARTMENT_CODE table with the data from the latestflat file.
[reply]

Back to Seekers of Perl Wisdom