2 tables with referential integrity

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!!

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

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

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.
--
Kind regards
Noëlla


"Jacquie" wrote:

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!!

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.

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

"Noëlla Gabriël" wrote in message
...
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.
--
Kind regards
Noëlla


"Jacquie" wrote:

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!!

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



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

Thanks, Graham!

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

Jeff B.

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



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

You're welcome, Jeff!

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

Cheers,
Graham

"Jeff Boyce" wrote in message
...
Thanks, Graham!

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

Jeff B.

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



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

Yes! Good thinking, Fred!

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

--
Cheers,
Graham M

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