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 |