#1
  1. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2009
    Posts
    334
    Rep Power
    246

    Lookup Tables / FK Contraints / Cyclical Updates


    Hi,

    Is there a correct way of using a lookup table in a DB schema as a CHECK constriant for multiple fields in a single record while defining it as a functional dependency? (FK)

    Do I have to handle global updates manually as you cannot have FK contraints agaisnt multiple fields in a record using a lookup table?

    If I try to use the lookup table via an FK functional dependency, SQL compliants that 'may cause cycles or multiple cascade paths'.

    How do you define a FK constraint to a lookup table, with functional dependency to maintain data integrity against mutiple fields in the same record without this issue?

    Thanks,
    1DMF.
    Free MP3 Dance Music Downloads

    To err is human; To really balls things up you need Microsoft!
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,026
    Rep Power
    4210
    Originally Posted by 1DMF
    Is there a correct way of using a lookup table in a DB schema as a CHECK constriant for multiple fields in a single record while defining it as a functional dependency? (FK)
    perhaps you'd better give a specific example

    a FK can be a composite key, i.e. consisting of multiple columns

    is that what you mean?
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  4. #3
  5. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2009
    Posts
    334
    Rep Power
    246
    hey r937,

    No , sorry, what I mean is...

    I have a table (the child), it has two fields 'Initial_Rating' , 'Final_Rating', they both are FK's to the 'Ratings' lookup table.

    If the ratings table was to be updated say changing the value of 'Good' from 1 to 3 , I would want to cascade update all records in the child table with the new value in both 'Initial_Rating' & 'Final_Rating' columns.

    Only it won't let me set those constraints with the error about multiple cascade paths.

    Yes i know the same record is trying to be updated in two places and which one should take presidence, but it doesn't matter, they both need to be applied and the order is irrelivant and the value is the same for both columns.

    It seems perfectly logical to me, but clearly i'm not doing something right.

    Thanks,
    1DMF
    Free MP3 Dance Music Downloads

    To err is human; To really balls things up you need Microsoft!
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,026
    Rep Power
    4210
    ah, yes, multiple cascade paths

    the way i get around this is to normalize the child table

    split off the two ratings columns into a separate table, so that they become two rows instead

    e.g.
    CREATE TABLE child_ratings
    ( parent_fk INTEGER
    , rating_type VARCHAR(9) -- 'initial' or 'final'
    , PRIMARY KEY ( parent_fk, rating_type )
    , rating INTEGER REFERENCES ratings (rating) ON UPDATE CASCADE
    )
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  8. #5
  9. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2009
    Posts
    334
    Rep Power
    246
    normalisation is not possible (well of course it is) however, too many applications , reports, queries etc all use the the 'child' table and expect the two rating fields to be in the same record / table.

    It would be a monumental task to change things to use a new normalised table structure.

    It also seems rather complicated and conveluted to normalise in this way, it would be no different than having a table with 'start_day' and 'finish_day' columns that wanted to use the same 'days' lookup table or 'months' for example.

    Also, only if intial / final are the same value would there even be a potential for 'multiple cascade paths', generally they are different values, just not always.

    I guess in this case I simply draw a 'virtual' relationship in the schema diagram and note that a manual SQL update will have to be performed if they ever decide to change the ratings value system, which has only happened once in 10 years, and that's what I did then!

    Thanks for your input.
    Free MP3 Dance Music Downloads

    To err is human; To really balls things up you need Microsoft!

IMN logo majestic logo threadwatch logo seochat tools logo