Multiple Cyclical Cascade Paths Error

Databases
21

So with this newfound understanding, in the relationship between PK entry_id and FK comment_status_eid, the UPDATE RULE should be set to what exactly? Cause the UPDATE RULE is only looking at a record’s PK for updates, right? And this field is an auto-incrementing number, which cannot be directly updated, correct?

BTW, if they had just called “NO ACTION” by a better name like “PREVENT RULE” then this would have been fine for me! PREVENT RULE makes it sound like "If the PK Rule (Update/Delete) can’t be done, then PREVENT it from happening. “NO ACTION” sounds like it says “If the rule can or can’t be done, just don’t bother doing anything to the FK”

Then again, what kind of integrity is that? :slight_smile:

22

lol! One true lookup table. :slight_smile: Yeah, I hear some friends call them lookups, others call them validation sets, so many monikers.

You’re saying that using a unified lookup table is not the best DB design choice then? I guess if I had a field that wanted a status of enabled/disabled, rather than making a table to hold 2 records, I would just make it a bit field instead.

23

OH!!! lol. “No Action” always sounded like “just don’t do anything for this rule” You’re saying that it means instead to allow NO ACTION to be performed.

I was gonna say, having a 0 in the field when a record is removed is kinda pointless for me

24

no, it means that the deletion would be disallowed

25

you ~should~ let the database take care of your cascades

you ~don’t~ need to have two paths to update/delete the ~same~ downstream row

26

RELATIONSHIPS - You can defined on a relationship, a condition when one field’s record is either updated or deleted. (I’m not positive on determining of the 2 tables, which field it’s looking at for updates/deletes in order to propagate changes to the other table.

ie. in the Posts.post_slug and Comments.post_slug example, if you put a CASCADE UPDATE on the relationship, is it watching for the Posts.post_slug to update, and then propagate changes, or is it watching the Comments.post_slug for changes?

Either way, it comes down to 2 rules: DELETE RULE and UPDATE RULE. Each can be set to one of 4 options: DO NOTHING, CASCADE (UPDATE/DELETE), SET NULL or SET DEFAULT.

The DELETE RULE happens when you DELETE the (?) record. The UPDATE RULE happens when you UPDATE the (?) record.

For NO ACTION, nothing happens. I don’t know why you’d create a relationship if nothing was going to happen, maybe you can enlighten me?

For SET NULL, the field has to support NULL values and on UPDATE/DELETE, the other table’s field is set to NULL

For SET DEFAULT, the field has to have a DEFAULT set and on UPDATE/DELETE, the other table’s field is set to its DEFAULT.

For CASCADE, when you UPDATE, the value has to be updatable (you can’t update a seeded field) and when you change the value, the other table updates as well. (Like if you updated the post_slug of a post, all comments for that post will update their post_slug to the same value) When you CASCADE and DELETE, all records referencing that first table’s field are deleted as well. (Like if you delete a post, all comments on that post get deleted too)

Special note, in order for 2 fields to be relational, they need to support the same datatype. If your Posts.post_slug is a varchar(50) and a relational field in Comments.post_slug is varchar(40), if the value set in Posts.post_slug ever exceeded 40 characters, the Comments.post_slug would not be able to properly maintain the relation.

So how am I doing so far, Rudy?

27

well, that sure sounds like “one true lookup table (OTLT)” and on that basis i wouldn’t condone such a design

if you were to use proper tables (like a “states” table for states, and a “statuses” table for statuses), then i daresay this entire thread would never have happened

anyhow…

if you had a post status “Published with Commenting” and several posts had this status, under what circumstances would you say it was acceptable to delete the status?

i personally think NO ACTION would be better than setting the comment status id to 0

besides, i’ll bet 0 isn’t a valid status code, so the FK would be violated

28

no, that’s not true

everything’s great as long as there is no more than one cascade to the same table

you can still do your cascades

you just can’t cascade a delete or update via more than one path to the same target table

29

I guess I might. I found this, which talks well and above my level of understanding for both the CAUSE and WORKAROUND options.

Makes it sound as if SQL simply doesn’t support what I’m trying to do, as logical as it is made.

I removed all relationships from the DB and was able to make a relation from Comments to Posts, Posts to Entries and Entries to Comments (in a circle). Everything’s great as long as all UPDATE/DELETE rules are set to NO ACTION. (which is counterproductive)

30

Maybe clearing up my understanding in what I’ve stated that is wrong will allow me to work on the DB design and get around this error.

Let’s get some terminology out of the way.

PRIMARY KEY - A field that is used to uniquely identify a record in that table. For example, in my Posts table, the Primary Key would be the post_slug field, because it contains a unique varchar that can be used to identify a particular record. PK’s cannot contain duplicate info. When using automatically generated numbers (seeded), this value is created automatically but cannot be changed for updates or specified on inserts (though a record can be deleted).

FOREIGN KEY - The field in another table that links to the primary key of another table. This is used for the cross reference. For example, in the Posts table, the post_slug is the PK, and in the Comments table the post_slug is the FK. Because the value between these fields and records match, a relation is made between the tables.

Am I right so far?

31

right

how do you feel about writing sql code to create the foreign keys? i know you’re using some sort of GUI, but would you consider taking control of things explicitly here, so that you can get it resolved?

32

From that point I need to make a relationship between am_posts.post_slug (PK) to am_comments.post_slug.

Both fields are varchar(128) not null, so if the PK is deleted, I want the FK to be set to default (the value ‘0’) and if the PK is updated (ie, changing the value of the post_slug) then that should CASCADE to the am_comments.post_slug.

33

no problem, no need to apologize :slight_smile:

so what did you try next, that gives an error?

34

I’ve never once in my whole life created a table using just the SQL. I’ve always used the UI, so I have no idea what parts of the SQL code are required and what parts were more optional.

Sorry about that.

35

Got it.

So ON UPDATE of the PK, [DO THIS] to the FK (where [DO THIS] can be ‘Nothing’, ‘Cascade’, ‘Set Default’ or ‘Set Null’.

OK. Let me go on this newfound understanding and see if I can setup the relationships without getting multiple cascading errors now.

=============================================

While I’m at that, is it OK in my example to use the varchar value of the post_slug between the Posts and Comments table where for the Posts table it is the PK and for the Comments it is the FK. I figure, there’s no point in having a seeded identity field called “post_id” on the Posts table because the post_slug is already maintaining unique record identification.

36

… wha?

Entries.entry_id is a PK. It is supposed to FK to Posts.post_status_eid and Comments.comment_status_eid.

You said earlier, the referencing fields (FK post_status_eid and FK comment_status_eid) are what’s updated when the referenced field (PK entry_id) is either Updated or Deleted, right?

37

the foreign key does the referencing, the primary or unique key is referenced

38

Ah! So with this IDENTITY INSERT you CAN specify an insert value, and as long as it’s one that wasn’t already in use (due to unique constraints) SQL will continue the seeding from the provided value.

I always thought that the seeded identity fields were a “set once and let it go” with only deletes able to be performed. Does this mean if I delete a record that had an ID of 2, I could force an insert of 2 and it would populate that field and continue forward again (skipping over latter records that were previously populated)?

And you’re saying that the RELATIONSHIP is what is responsible for ensuring that the datatypes between the fields is compatible. OK. That makes sense.

Also, thanks for clarifying that the ON UPDATE/DELETE. But I don’t quite understand how I can tell which column is the REFERENCED and which is REFERENCING.

I do these updates through the SQL Enterprise manager rather than via actual code, so all I see is: "Right click on table, select RELATIONSHIPS…, create a relationship between 2 tables and their fields, and then change the drop downs on a relationship for the ON UPDATE and ON DELETE rules.

Is there an easy way for me to discern which is REFERENCED and which is REFERENCING?

39

this looks okay to me, and because there is no ON DELETE or ON UPDATE clause, these will use the defaults, which in both cases is NO ACTION

but this contradicts what you had in post #1, namely[indent]Comments.comment_status_eid (FK) <-> (FK) Entries.entry_id
On Unique/ID Delete -> Set FK to NULL
On Unique/ID Update -> No Action[/indent]
although i am at a loss to tell you why :slight_smile:

where did your original script come from?

40

wow, i don’t know where to begin to correct some of those ideas you picked up along the way

are you still trying to fix the error you got in post #1?

or is this thread morphing into something else?