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.

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?

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

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
)

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.