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. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|