A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Access not enforcing referential integrity!!!



 
 
Thread Tools Display Modes
  #1  
Old April 15th, 2010, 04:26 AM posted to microsoft.public.access.tablesdbdesign
Danny
external usenet poster
 
Posts: 385
Default Access not enforcing referential integrity!!!

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?
  #2  
Old April 15th, 2010, 04:48 AM posted to microsoft.public.access.tablesdbdesign
Tom van Stiphout[_2_]
external usenet poster
 
Posts: 1,653
Default Access not enforcing referential integrity!!!

On Wed, 14 Apr 2010 20:26:01 -0700, Danny
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?

  #3  
Old April 15th, 2010, 06:00 AM posted to microsoft.public.access.tablesdbdesign
Sylvain Lafontaine[_2_]
external usenet poster
 
Posts: 247
Default Access not enforcing referential integrity!!!

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" wrote in message
...
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?



  #4  
Old April 15th, 2010, 08:59 AM posted to microsoft.public.access.tablesdbdesign
Danny
external usenet poster
 
Posts: 385
Default Access not enforcing referential integrity!!!

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" wrote in message
...
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?



.

  #5  
Old April 15th, 2010, 03:39 PM posted to microsoft.public.access.tablesdbdesign
Roger Carlson
external usenet poster
 
Posts: 824
Default Access not enforcing referential integrity!!!

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/...UBED1=ACCESS-L


"Danny" wrote in message
...
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" wrote in message
...
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?



.



  #6  
Old April 15th, 2010, 05:38 PM posted to microsoft.public.access.tablesdbdesign
John W. Vinson
external usenet poster
 
Posts: 18,261
Default Access not enforcing referential integrity!!!

On Thu, 15 Apr 2010 00:59:02 -0700, Danny
wrote:

There is no way to allow or prohibit null values in an Access table.


Actually there is: set the field's Required property to Yes in table design
view.

--

John W. Vinson [MVP]
  #7  
Old April 15th, 2010, 06:38 PM posted to microsoft.public.access.tablesdbdesign
Danny
external usenet poster
 
Posts: 385
Default Access not enforcing referential integrity!!!

!!! The default value of 0 is not working on subforms. I've set the default
value in both the table and the subform, but it doesn't seem to get set when
I attempt to enter data into the subform when the parent form record is
empty. Here is what happens:

- Main form record is empty
- Subform record is empty, and default value of 0 displays in the FK field
- I start typing data into the subform record, while main form record is
still empty
- FK field value changes from 0 to NULL

If I make the FK field Required, in addition to setting the default value to
0, then here's what happens:

- Main form record is empty
- Subform record is empty, and default value of 0 DOESN'T display in the FK
field
- I start typing data into the subform record, and immediately a prompt
tells me that I must enter a value in the FK field
- I click through the prompt, and finish entering data in the subform record
(main form record still empty)
- When I attempt to move off of the new subform record, I get a prompt
telling me that I can't change or add record because a related record is
required in the parent table

So this will do - it will prevent creation of orphaned records. But it seems
an awful lot of redundant field property values...



"Roger Carlson" wrote:

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/...UBED1=ACCESS-L


"Danny" wrote in message
...
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" wrote in message
...
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?


.



.

  #8  
Old April 15th, 2010, 11:34 PM posted to microsoft.public.access.tablesdbdesign
John W. Vinson
external usenet poster
 
Posts: 18,261
Default Access not enforcing referential integrity!!!

On Thu, 15 Apr 2010 10:38:06 -0700, Danny
wrote:

!!! The default value of 0 is not working on subforms. I've set the default
value in both the table and the subform, but it doesn't seem to get set when
I attempt to enter data into the subform when the parent form record is
empty. Here is what happens:

- Main form record is empty
- Subform record is empty, and default value of 0 displays in the FK field
- I start typing data into the subform record, while main form record is
still empty
- FK field value changes from 0 to NULL


Erm? What's the Master and Child Link Field? The Master should be the PK field
on the parent form, the Child should be the FK. I'd say that the FK field
should be required but that you should *not* use a zero default; that's more a
pain in the neck than any sort of benefit (since it will always be wrong and
need to be edited).

I suspect your master/child link isn't set up, or isn't working correctly.
--

John W. Vinson [MVP]
  #9  
Old April 16th, 2010, 01:14 PM posted to microsoft.public.access.tablesdbdesign
Fred
external usenet poster
 
Posts: 1,451
Default Access not enforcing referential integrity!!!

Danny,

You mentioned entering on a subform in your last post.

Assuming that the subform is actually being used as a subform, the PK value
of the parent record is automaticaly loaded into the FK of the child record
when the user creates a new record in the the subform.

This is also the common way that this issue gets taken care of.

Fred

  #10  
Old April 16th, 2010, 03:33 PM posted to microsoft.public.access.tablesdbdesign
BruceM via AccessMonster.com
external usenet poster
 
Posts: 448
Default Access not enforcing referential integrity!!!

Assuming the Link Child and Link Master properties of the subform control are
properly set, as John pointed out.


Fred wrote:
Danny,

You mentioned entering on a subform in your last post.

Assuming that the subform is actually being used as a subform, the PK value
of the parent record is automaticaly loaded into the FK of the child record
when the user creates a new record in the the subform.

This is also the common way that this issue gets taken care of.

Fred


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...esign/201004/1

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 09:42 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.