Confused about Relationships

[Jonathan Wood]

Most of my DB experience is many years ago with Access. SQL's fine but I
find managing relationships awkward and confusing.

Anyway, I've created a table that is on the many side of a one-to-many
relationship. So it contains a foreign key that references a primary key in
the table that is on the one side. I think I understand all this. But now I
want to implement cascading deletes. I can set this, but all of a sudden,
I'm not 100% sure which way that goes.

Do cascading deletes always imply that the row in the foreign table gets
deleted when the matching row with the primary key gets deleted? I think
this is right. But is it ever possible to go the other way? Is this ever
specified, or is it always implied that it will work as I have described?

Thanks.

 

[PvdG42]

Most of my DB experience is many years ago with Access. SQL's fine but I
find managing relationships awkward and confusing.

Anyway, I've created a table that is on the many side of a one-to-many
relationship. So it contains a foreign key that references a primary key
in the table that is on the one side. I think I understand all this. But
now I want to implement cascading deletes. I can set this, but all of a
sudden, I'm not 100% sure which way that goes.

Do cascading deletes always imply that the row in the foreign table gets
deleted when the matching row with the primary key gets deleted? I think
this is right. But is it ever possible to go the other way? Is this ever
specified, or is it always implied that it will work as I have described?

Thanks.

This (and others) article may be helpful.

http://msdn.microsoft.com/en-us/library/aa933119(sql.80).aspx

If you go here:

http://www.microsoft.com/sql/default.mspx

then, search for "cascading deletes", you'll find numerous articles.

 

[Jonathan Wood]

Marc,

No, you got it backwards Cascading delete will be defined so that
if the parent row is deleted (the one you reference with the foreign
key), all the rows in the child table that reference it also get
deleted. After all, with the parent row they reference gone - what are
they referencing??

Okay, yeah. I used the incorrect terminology. I'm still getting used to the
idea of terms like "foreign key."

In many cases, a cascading delete can make good business sense.

Right, I've used the basic idea before. But, all of a sudden I drew a blank
as to what determines which direction the cascades go.

Thanks.

 

[Jonathan Wood]

Sorry. I read that several times but am still not sure what you are saying.