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

PvdG42
Wed May 07 09:30:44 CDT 2008

"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
Thu May 08 00:15:35 CDT 2008

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

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

E.g. if you have an Invoice table as the parent table, and an
InvoiceItems table containing the items for the invoice which
references the Invoice table by a foreign key (e.g. invoice number or
something), if you delete the invoice no. 123 - what should happen to
all the items in the InvoiceItems table referencing that invoice?
Setting the relationship to "cascade delete" will delete all child
rows (the InvoiceItems) when a parent row (the invoice) is deleted.

You could also do nothing, but then you end up with invoice items
referencing a non-existing invoice, or you could set the invoice
item's "invoice number" to NULL - but then you potentially end up
"ghost" or "zombie" items that do not belong to any invoice.....

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

HTH
Marc

Cor
Thu May 08 07:18:30 CDT 2008

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
>

Jonathan
Tue May 13 14:49:51 CDT 2008

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

Jonathan
Tue May 13 14:50:30 CDT 2008

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
news:D8EC0170-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
>>
>