2 tables with referential integrity

[Jacquie]

I have 2 tables with referential integrity: contacts and orgs. it's a
many-to-one, with many contacts connected to one org. however, I also have
some contacts with no org, but when I try to add them, access tells me I have
to have an org to proceed. BUT THERE IS NO ORG!! org is not required in the
contacts table. what do I have to do? Thanks!!

 

[Jeff Boyce]

Jacquie

I believe that if you tell Access to enforce referential integrity between
those two, you're telling it that you cannot have a Contact record without a
corresponding Org record.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Noëlla Gabriël]

Hi,

the referential integrity dictates that you cannot have a contact without an
org. A much used solution is to create an org named "Private" or "No
organisation" and link all contacts without org to that one.

 

[Fred]

One could argue that Access referential integrity is not appropriate for this
set of tables. After all, it does nothing more than say"a contact can not
exist without an organization" which directly conflicts with the real world
situation that you are databasing.

 

[Jeff Boyce]

I agree with Fred -- if the real world doesn't fit, don't shoehorn it in!
Adjust the system to match the scenario.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Graham Mandeno]

Hi Jacquie

Contrary to what the others have said, referential integrity does NOT
prevent a Null value is a foreign key field. It simply ensures that any
non-Null value in the FK field has a corresponding PK value in the one-side
table. (And, correspondingly, prevents you deleting a record in the one-side
table that has corresponding FK values on the many-side.)

My first suggestion would be to check that Org is not "Required" in the
contacts table, but it seems you have already verified that. I feel there
must be something else requiring that field to have a value. Is it perhaps
part of a required index (primary key maybe)?

Something else you could try is temporarily removing referential integrity
to verify that you can then add a record without an Org value. My guess is
that you will still not be able to.

 

[Jeff Boyce]

Thanks, Graham!

I haven't run across that variation, so your post satisfies my "what did you
learn new today?" requirement!

Jeff B.

Hi Jacquie

Contrary to what the others have said, referential integrity does NOT
prevent a Null value is a foreign key field. It simply ensures that any
non-Null value in the FK field has a corresponding PK value in the
one-side table. (And, correspondingly, prevents you deleting a record in
the one-side table that has corresponding FK values on the many-side.)

My first suggestion would be to check that Org is not "Required" in the
contacts table, but it seems you have already verified that. I feel there
must be something else requiring that field to have a value. Is it
perhaps part of a required index (primary key maybe)?

Something else you could try is temporarily removing referential integrity
to verify that you can then add a record without an Org value. My guess
is that you will still not be able to.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

I have 2 tables with referential integrity: contacts and orgs. it's a
many-to-one, with many contacts connected to one org. however, I also
have
some contacts with no org, but when I try to add them, access tells me I
have
to have an org to proceed. BUT THERE IS NO ORG!! org is not required in
the
contacts table. what do I have to do? Thanks!!

 

[Graham Mandeno]

You're welcome, Jeff!

I figure that day I don't learn something I must be dead

Cheers,
Graham

Thanks, Graham!

I haven't run across that variation, so your post satisfies my "what did
you learn new today?" requirement!

Jeff B.

Hi Jacquie

Contrary to what the others have said, referential integrity does NOT
prevent a Null value is a foreign key field. It simply ensures that any
non-Null value in the FK field has a corresponding PK value in the
one-side table. (And, correspondingly, prevents you deleting a record in
the one-side table that has corresponding FK values on the many-side.)

My first suggestion would be to check that Org is not "Required" in the
contacts table, but it seems you have already verified that. I feel
there must be something else requiring that field to have a value. Is it
perhaps part of a required index (primary key maybe)?

Something else you could try is temporarily removing referential
integrity to verify that you can then add a record without an Org value.
My guess is that you will still not be able to.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

I have 2 tables with referential integrity: contacts and orgs. it's a
many-to-one, with many contacts connected to one org. however, I also
have
some contacts with no org, but when I try to add them, access tells me I
have
to have an org to proceed. BUT THERE IS NO ORG!! org is not required in
the
contacts table. what do I have to do? Thanks!!

 

[Fred]

Graham pointed out a possibility / way that it should work that I forgot
about.

My first guess on what to check if it isn't working is that if the FK is a
numeric field, to make sure that the default value for that field is null and
not zero.

 

[Graham Mandeno]

Yes! Good thinking, Fred!

Jacquie seems to have left us, but it's an interesting discussion anyway

 

[Angela]

If you choose a one-to-many relationship and select the join property
"Include ALL records from Contacts and only those records from Orgs where the
joined fields are equal", wouldn't that allow you to have records in Contacts
without corresponding records in Orgs? Are you saying that if you choose
"Only include records where the joined fields in both tables are equal", but
there are null entries in the joined field in Contacts, you can avoid having
a related record in Orgs?

And I have a general "newbie" question: If one doesn't need to enforce
referential integrity, is there any benefit to creating these table
relationships?

 

[Graham Mandeno]

Hi Angela

As far as I know, changing the join property to "Include ALL the records
from..." has no effect whatsoever on the actual relationship or on
relational integrity. It simply means that whenever you add those two
tables to a query in design view, the join is created, by default, as an
OUTER join (with an arrow on one end), not as an INNER join.

As I said in another post, RI does not prevent you having a null value in
the foreign key of the many-side table, no matter what the join type.

And for your "newbie" question, I believe you would gain some advantage in
speed of queries with a non-RI relationship, but I can't confirm that for
certain. Personally, I find that relationships where RI is not appropriate
are *extremely* rare.

 

[Fred]

About once a month I get an intelligent thought and that just happenned to be
the day. My thinking originally got derailed by accepting the "can't add
records due to referential integrity" premise as categorically true.