Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Referential integrity

Re: Referential integrity

From: Andreas Moroder <Andreas.moroder_at_[nospam>
Date: Thu, 01 Jun 2006 16:27:17 +0200
Message-ID: <e5n0rb$jjt$1@news.dtag.de>


> well, maybe.
>
> if you have a single code table with a 2-part pk, column 1 is the code type,
> and column 2 is the code, then all your lookup codes code be 2-column FKs,
> with the first column as the code type (with a default value and a check
> constraint to enforce one specific code type) and the 2nd column as the
> actual code
>

Hello Mark,

I talked to my colleague about this solution this morning. We discarded it because I don't like the idea to have that many more fields.

>
> Some rambling thoughts on another possible implementation compromise(watch
> out for stray flack on this one):
>
> Have one code table with codes segregated by ID ranges (I did say
> compromise, didn't I?) and have single-column FK's referencing this table
> that also have check constraints enforcing codes within the correct range --
> fewer tables, fewer columns, and no issue with running out of codes within a
> range (use the decimal portion for the actual code, i.e. 1.1, 1.2, 1.3,
> etc.) but it introduces inteligence into the key value (impure!) and hides
> the code type in the check constraint -- if the code type is an actual
> column, then it can be referenced by application code when displaying
> reference lists of codes, if it is in a check constraint, there's a good
> change the application code may hard-code the code type -- which would be
> unlikely to change, but adds some cost to code maintenance and QA (although
> the table's check constraint would not allow a code in the wrong range to be
> used, even if the applicaction offers a list of codes in the wrong range.
>

Wow. Not a bad idea.
The only problem is that in the development environment I use it would become to hard to hide the first part of the key.

I don't understand why it is not possible to create a referential integrity constraint that uses a combination of fields and constants. This would solve this problem easily, and would to a certain degree be clean too.

I tried also with functional indices, but they can not be used for referential integrity even if they are unique

Another solution could be to use triggers that do the check, but this is not really clean.

Thank you and sorry for my bad english.

Andreas Received on Thu Jun 01 2006 - 09:27:17 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US