Cyclical cascading updates, how to maintain integrity

[1DMF]

Hi,

I have a problem creating an FK to a lookup table because SQL is saying

may cause cycles or multiple cascade paths

Yes, this is correct!

I have a table that has two fields, 'Initial_Rating', 'Final_Rating' , they both need to use the lookup table 'Ratings'.

If the ratings lookup table was altered, this should cascade the update to both fields joined in the the child record.

what's wrong with that?

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Free Electronic Dance Music Download

 

[SQLScholar]

Ok.

Scenario.

Both 'Initial_Rating' and 'Final_Rating' point to the same record in the ratings table. You update both to different values.

Which would win?

----------------------------------------

http://www.fountain.me.uk

Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind - Bernard Baruch

Computer Science is no more about computers than astronomy is about telescopes - EW Dijkstra
----------------------------------------

 

[SQLScholar]

answer here seems good:

http://stackoverflow.com/questions/...nt-may-cause-cycles-or-multiple-cascade-paths

----------------------------------------

http://www.fountain.me.uk

Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind - Bernard Baruch

Computer Science is no more about computers than astronomy is about telescopes - EW Dijkstra
----------------------------------------

 

[SQLScholar]

my scenario was poor - much better discussion on the other forum


----------------------------------------

http://www.fountain.me.uk

Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind - Bernard Baruch

Computer Science is no more about computers than astronomy is about telescopes - EW Dijkstra
----------------------------------------

 

[1DMF]

So the short anwser is, it can't be done.

SQL sees them as record level updates not field level updates.

There is technically no FD to this constraint, it is simply a lookup table shared by fields in a record.

It seem the only way round this is to use triggers?

The only reason I wanted to cascade updates is if they changed the value of a rating record in the ratings table all records that had the old value would be updated in the rest of the DB.

I can see this isn't quite a simple as I first thought, as all FK contraints seem to have a functional dependency, which isn't true for lookup tables, well it is and it isn't, depends on how they are used by a record it would seem!

They are for providing a mechanism for data selection, and to define them as a contraint they would be a CHECK constraint not a functional dependency.

Is there a correct way of using a lookup table in the schema as a CHECK constriant for multiple fields in a single record without defining it as a functional dependency?

And does this mean I have to handle global updates manually as you cannot have FK contraints the way I am using the lookup table?







"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Free Electronic Dance Music Download