Confused about Relationships

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
SoftCircuits Programming
http://www.softcircuits.com

"Jonathan Wood" <jwood@softcircuits.com> wrote in message
news:%23RsTFp%23rIHA.524@TK2MSFTNGP05.phx.gbl...
> 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
> SoftCircuits Programming
> http://www.softcircuits.com
>

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??

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
SoftCircuits Programming
http://www.softcircuits.com

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

--
Jonathan Wood
SoftCircuits Programming
http://www.softcircuits.com

"Cor Ligthert[MVP]" <notmyfirstname@planet.nl> wrote in message
news8EC0170-C6B7-4E6A-8EE7-C74A6D4E3A27@microsoft.com...
> Jonhathan,
>
> What do you want to do as a row is a childrelation of two different
> parentrows?
>
> Cor
>
> "Jonathan Wood" <jwood@softcircuits.com> schreef in bericht
> news:%23RsTFp%23rIHA.524@TK2MSFTNGP05.phx.gbl...
>> 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
>> SoftCircuits Programming
>> http://www.softcircuits.com
>>
>