Prev: Vehicle Waiver Database
Next: Auto Populated a Table From SQL View When New Record Shows in View
From: Danny on 14 Apr 2010 23:26
Howdy.

Using Acc 2007. Have two tables related one-to-many. Ref. integrity being
enforced, without any cascading updates or deletes. Data in both tables.

Problem: I can add new records to the many-side table, without any value for
the foreign key, and the new record is created without error or prompt from
Access. I can do this both in the child table directly, and using a
form/subform.

On the other hand, if I create a new child record and enter a FK value that
doesn't exist in the parent table, I get a message saying that a related
record in the parent table is required, and Access prohibits creation of
orphan.

What the heck is going on?
From: Tom van Stiphout on 14 Apr 2010 23:48
On Wed, 14 Apr 2010 20:26:01 -0700, Danny
<Danny(a)discussions.microsoft.com> wrote:

I don't quite follow you: the "many-side table" and the "child table"
are the same thing.

-Tom.
Microsoft Access MVP


>Howdy.
>
>Using Acc 2007. Have two tables related one-to-many. Ref. integrity being
>enforced, without any cascading updates or deletes. Data in both tables.
>
>Problem: I can add new records to the many-side table, without any value for
>the foreign key, and the new record is created without error or prompt from
>Access. I can do this both in the child table directly, and using a
>form/subform.
>
>On the other hand, if I create a new child record and enter a FK value that
>doesn't exist in the parent table, I get a message saying that a related
>record in the parent table is required, and Access prohibits creation of
>orphan.
>
>What the heck is going on?
From: Sylvain Lafontaine on 15 Apr 2010 01:00
Referential integrity is checked only for Non-Null values; so this is the
expected behavior here. If you don't want to have any Null value in your
foreign key, set it up as Not-Null in order to forbid null value in the
first place.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)


"Danny" <Danny(a)discussions.microsoft.com> wrote in message
news:1BA904E7-70E4-4F61-AE54-247CB5268418(a)microsoft.com...
> Howdy.
>
> Using Acc 2007. Have two tables related one-to-many. Ref. integrity being
> enforced, without any cascading updates or deletes. Data in both tables.
>
> Problem: I can add new records to the many-side table, without any value
> for
> the foreign key, and the new record is created without error or prompt
> from
> Access. I can do this both in the child table directly, and using a
> form/subform.
>
> On the other hand, if I create a new child record and enter a FK value
> that
> doesn't exist in the parent table, I get a message saying that a related
> record in the parent table is required, and Access prohibits creation of
> orphan.
>
> What the heck is going on?


From: Danny on 15 Apr 2010 03:59
I did not know this - that null foreign key values are allowed. Wow. I've
worked with relational databases for over ten years, too. Hmm...

BUT: There is no way to allow or prohibit null values in an Access table.
For a text field, zero length strings can be allowed or prohibited, but not
null values. Another way to deal with this is to set Required to Yes for
these fields. But I have to do something, because I don't want users being
able to inadvertently enter data into a subform where the main form has no
data.

Thanks for the education in RI and null values :)

"Sylvain Lafontaine" wrote:

> Referential integrity is checked only for Non-Null values; so this is the
> expected behavior here. If you don't want to have any Null value in your
> foreign key, set it up as Not-Null in order to forbid null value in the
> first place.
>
> --
> Sylvain Lafontaine, ing.
> MVP - Windows Live Platform
> Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
> Independent consultant and remote programming for Access and SQL-Server
> (French)
>
>
> "Danny" <Danny(a)discussions.microsoft.com> wrote in message
> news:1BA904E7-70E4-4F61-AE54-247CB5268418(a)microsoft.com...
> > Howdy.
> >
> > Using Acc 2007. Have two tables related one-to-many. Ref. integrity being
> > enforced, without any cascading updates or deletes. Data in both tables.
> >
> > Problem: I can add new records to the many-side table, without any value
> > for
> > the foreign key, and the new record is created without error or prompt
> > from
> > Access. I can do this both in the child table directly, and using a
> > form/subform.
> >
> > On the other hand, if I create a new child record and enter a FK value
> > that
> > doesn't exist in the parent table, I get a message saying that a related
> > record in the parent table is required, and Access prohibits creation of
> > orphan.
> >
> > What the heck is going on?
>
>
> .
>
From: Roger Carlson on 15 Apr 2010 10:39
You can give your foreign key a Default Value (ie. 0 (zero) for a numeric)
so that it will not be Null when a new record is created.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


"Danny" <Danny(a)discussions.microsoft.com> wrote in message
news:1FABE623-10E5-44D9-95DD-E83543A0946C(a)microsoft.com...
>I did not know this - that null foreign key values are allowed. Wow. I've
> worked with relational databases for over ten years, too. Hmm...
>
> BUT: There is no way to allow or prohibit null values in an Access table.
> For a text field, zero length strings can be allowed or prohibited, but
> not
> null values. Another way to deal with this is to set Required to Yes for
> these fields. But I have to do something, because I don't want users being
> able to inadvertently enter data into a subform where the main form has no
> data.
>
> Thanks for the education in RI and null values :)
>
> "Sylvain Lafontaine" wrote:
>
>> Referential integrity is checked only for Non-Null values; so this is the
>> expected behavior here. If you don't want to have any Null value in your
>> foreign key, set it up as Not-Null in order to forbid null value in the
>> first place.
>>
>> --
>> Sylvain Lafontaine, ing.
>> MVP - Windows Live Platform
>> Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
>> Independent consultant and remote programming for Access and SQL-Server
>> (French)
>>
>>
>> "Danny" <Danny(a)discussions.microsoft.com> wrote in message
>> news:1BA904E7-70E4-4F61-AE54-247CB5268418(a)microsoft.com...
>> > Howdy.
>> >
>> > Using Acc 2007. Have two tables related one-to-many. Ref. integrity
>> > being
>> > enforced, without any cascading updates or deletes. Data in both
>> > tables.
>> >
>> > Problem: I can add new records to the many-side table, without any
>> > value
>> > for
>> > the foreign key, and the new record is created without error or prompt
>> > from
>> > Access. I can do this both in the child table directly, and using a
>> > form/subform.
>> >
>> > On the other hand, if I create a new child record and enter a FK value
>> > that
>> > doesn't exist in the parent table, I get a message saying that a
>> > related
>> > record in the parent table is required, and Access prohibits creation
>> > of
>> > orphan.
>> >
>> > What the heck is going on?
>>
>>
>> .
>>


 |  Next  |  Last
Pages: 1 2 3
Prev: Vehicle Waiver Database
Next: Auto Populated a Table From SQL View When New Record Shows in View