Confused about Relationships

  • Thread starter Thread starter Jonathan Wood
  • Start date Start date
J

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.
 
Jonathan Wood said:
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.
Click to expand...

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.
 
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??
Click to expand...

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.
Click to expand...

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.
 
Back
Top