Re: Referential integrity


"Andreas Moroder" <Andreas.moroder@[nospam]sb-brixen.it> wrote in message
news:e5m2kr$ml4$1@xxxxxxxxxxxxxxx
: Hello,
:
: i have a big table with 25 fields that should be checked for their
: contents. Because I also need the description of this values I should
: build 25 table and activate referential integrity.
:
: When I go on this way I will end with hundreds of very small tables.
:
: Is there another way to guarantee referential integrity ?
:
: I tried with functional indices on one single lookup table, but they can
: not be used a foreign key.
:
: Bye
: Andreas

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

ie:

create table codes (
type_id number
, code_id number
, abbrev varchar2(12) not null
, descr varchar2(30) not null
, constraint codes_pk primary key (type_id,code_id)
, constraint codes_abbrev_uk unique (type_id,abbrev)
, constraint codes_descr_uk unique (type_id,descr)
)
/

create table personnel (
personnel_id number constraint personnel_pk primary key
, status_type_id number default 1
, status_code_id number
, constraint chk_status_type_id check (status_type_id = 1)
, constraint personnel_status_fk foreign key (status_type_id,status_code_id)
references codes
)
/

This results in one extra column per status code per table (rather than one
table per status code), but gives you full DRI.

Note that this allows for optional FKs even though CODE_TYPE_ID will be
forced to a specific value (if nulls exist in a portion of a multi-column
FK, RI is not enforced -- ANSI standard, IIRC)

There should also be a CODE_TYPES tables with an FK from the TYPE_ID column
in the CODES table (but not from the *TYPE_ID columns of the other tables --
this is covered by the check constraint)

This type of technique falls into the category of logical implementation vs.
conceptual design (or what some would call physical implementation vs
logical design) -- I would not recommend modeling the code tables this way
in the E/R diagram, but at implementation time, this could be the way the DB
is actually built.

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.

++mcs


.

Relevant Pages

  • Re: Create Table Issues
    ... unique index on BookID. ... > CONSTRAINT PubPK PRIMARY KEY ... > CONSTRAINT PubFK FOREIGN KEY REFERENCES Publisher ...
    (microsoft.public.access.gettingstarted)
  • Re: Check Constraints using a subquery - SSrvr 2000
    ... typically refer to the primary key of the referenced table, ... constraint or unique index references are also allowed. ... >> One usually uses a foreign key constraint to enforce a required ...
    (microsoft.public.sqlserver.programming)
  • Re: Modelling Disjoint Subtypes
    ... the only constraints one should have are foreign key references. ... I think the answer is that a foreign key constraint enforces a constraint between columns in two tables. ... these tables also have a primary key K which references K as a foreign key. ...
    (comp.databases.theory)
  • Re: unknown symbol in ER diagram
    ... main_ID INTEGER NOT NULL PRIMARY KEY ... CONSTRAINT fk__sub1 FOREIGN KEY ... REFERENCES Main ... ALTER TABLE Sub1 ADD CONSTRAINT ch__sub1 ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Referential integrity
    ... 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 ... Have one code table with codes segregated by ID ranges 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 ...
    (comp.databases.oracle.misc)
Loading