Question on how I can use ForeignKeyConstraint

28 Jan 2005 6:25 PM

Harry Keck

I am using ForeignKeyConstraints so that when I change a parent row's key,
the child rows are updated with the appropriate key.

The problem that I am running into is that I would like to be able to add a
row to a table with a value that would not fit a stated constraint. So, I
could add a child row with a value of 5, but there would be no corresponding
parent row with that key. If I have a ForeignKeyConstraint in place, an
exception is thrown in this situation.

How can I get the benefit of auto updating values, without getting
execeptions for creating child rows with no parent?

28 Jan 2005 8:15 PM

W.G. Ryan eMVP

The whole purpose of having the key in place is to throw exceptions and
thereby eliminate the risk of orhpaned records. You can set the
EnforceConstraints property to false, but it's dangerous b/c if something
goes wrong or your forget to add that parent record - you'll have issues.
However if you remember to turn it back on when the parent value appears -
it should get you over the hump

I don't understand what benefit you are looking for though w/ Autoupdating
fields b/c they only update if there's a parent record - in theexample you
mention it doesn't seem that there would be if I understand you correctly.

--
W.G. Ryan, MVP

www.tibasolutions.com | www.devbuzz.com | www.knowdotnet.com

Show quoteHide quote

"Harry Keck" <HarryK***@discussions.microsoft.com> wrote in message
news:F2BAB40F-8AF5-4F9F-9890-3C19D7A75386@microsoft.com...
> I am using ForeignKeyConstraints so that when I change a parent row's key,
> the child rows are updated with the appropriate key.
>
> The problem that I am running into is that I would like to be able to add
a
> row to a table with a value that would not fit a stated constraint. So, I
> could add a child row with a value of 5, but there would be no
corresponding
> parent row with that key. If I have a ForeignKeyConstraint in place, an
> exception is thrown in this situation.
>
> How can I get the benefit of auto updating values, without getting
> execeptions for creating child rows with no parent?

31 Jan 2005 4:23 PM

Harry Keck

The place that this comes up is where I start with a dataset full of parents
and children, and then later I need to work with a dataset that is a subset
of the other one, where I may only have the parents or the children, but not
both. As I am creating this subset database the errors occur. This is why I
want the auto updating for when both are present, but I do not want errors
when I take one away.

If I set EnforceConstraints to false, will I get the auto updating, or does
that get turned off as well as the errors?

Thanks.

Show quoteHide quote

"W.G. Ryan eMVP" wrote:

> The whole purpose of having the key in place is to throw exceptions and
> thereby eliminate the risk of orhpaned records. You can set the
> EnforceConstraints property to false, but it's dangerous b/c if something
> goes wrong or your forget to add that parent record - you'll have issues.
> However if you remember to turn it back on when the parent value appears -
> it should get you over the hump
>
> I don't understand what benefit you are looking for though w/ Autoupdating
> fields b/c they only update if there's a parent record - in theexample you
> mention it doesn't seem that there would be if I understand you correctly.
>
> --
> W.G. Ryan, MVP
>
> www.tibasolutions.com | www.devbuzz.com | www.knowdotnet.com
> "Harry Keck" <HarryK***@discussions.microsoft.com> wrote in message
> news:F2BAB40F-8AF5-4F9F-9890-3C19D7A75386@microsoft.com...
> > I am using ForeignKeyConstraints so that when I change a parent row's key,
> > the child rows are updated with the appropriate key.
> >
> > The problem that I am running into is that I would like to be able to add
> a
> > row to a table with a value that would not fit a stated constraint. So, I
> > could add a child row with a value of 5, but there would be no
> corresponding
> > parent row with that key. If I have a ForeignKeyConstraint in place, an
> > exception is thrown in this situation.
> >
> > How can I get the benefit of auto updating values, without getting
> > execeptions for creating child rows with no parent?
>
>
>