Referential Integrity Issues

[Jacquie]

So I had this beautiful database with a contacts table and an orgs table.
Referential integrity/cascade update set on the relationship. I had to import
an excel spreadsheet with some contacts who had no orgs. It wouldn't let me
import unless I took off the referential integrity. So I did, thinking I
could add it back. But it still won't because it says there are no orgs for
some contacts. I even changed the join type from equal to "include all
records from contacts and only those from orgs that match" ... no dice. But I
should be able to have cons without orgs -- done it in other databases. And
another thing -- if I search for orgs that are null, I get nothing. If I
search for orgs <"a", I get 200. so what's up? can anyone point me in the
right direction? Thanks!!

 

[Keith Wilby]

So I had this beautiful database with a contacts table and an orgs table.
Referential integrity/cascade update set on the relationship. I had to
import
an excel spreadsheet with some contacts who had no orgs. It wouldn't let
me
import unless I took off the referential integrity. So I did, thinking I
could add it back. But it still won't because it says there are no orgs
for
some contacts. I even changed the join type from equal to "include all
records from contacts and only those from orgs that match" ... no dice.
But I
should be able to have cons without orgs -- done it in other databases.

Try using an outer join.

And
another thing -- if I search for orgs that are null, I get nothing. If I
search for orgs <"a", I get 200. so what's up? can anyone point me in the
right direction? Thanks!!

Lost me there, can you provide more information?

Keith.
www.keithwilby.co.uk

 

[Jacquie]

I did the outer join (all contacts and just orgs that matched) .... no luck
.....

Contacts with no organizations do not show up in queries that look for "is
null" in org, but 200 show up when I make the criteria <"a" instead ....

 

[Fred]

One good rule for troubleshooting is " when you get it narrowed down to where
there is no explanation" then look for something that you missed or are
miss-perceiveing"

Could you be mixing up join types set at the design level vs. set in the
query?

And on your "<a" situaiton, you mightr take a look at the data in your
linking fields. Also at exactly what org record(s) (if any) is/are showing
up when you do that search. That might help provide a clue.

For example, if your linking field in contacts is numeric (with a default
value of 0) and you have one record in orgs with "0" in the linking field,
they could all be linking to that one record.

 

[John W. Vinson]

I did the outer join (all contacts and just orgs that matched) .... no luck
....

Contacts with no organizations do not show up in queries that look for "is
null" in org, but 200 show up when I make the criteria <"a" instead ....

Check the table definition and see if "Allow Zero Length Strings" is set to
yes in this field's properties; equivalently, use a query criterion of

= ""

to see if the field contains an empty string. An empty string is DIFFERENT
from a NULL value and can cause just this kind of problem.

If you have referential integrity - what should a nonexistant (null, empty
string) Org link *to*?

 

[Piet Linden]

So I had this beautiful database with a contacts table and an orgs table.
Referential integrity/cascade update set on the relationship. I had to import
an excel spreadsheet with some contacts who had no orgs. It wouldn't let me
import unless I took off the referential integrity. So I did, thinking I
could add it back. But it still won't because it says there are no orgs for
some contacts. I even changed the join type from equal to "include all
records from contacts and only those from orgs that match" ... no dice. But I
should be able to have cons without orgs -- done it in other databases. And
another thing -- if I search for orgs that are null, I get nothing. If I
search for orgs <"a", I get 200. so what's up? can anyone point me in the
right direction? Thanks!!

I had this problem once. Basically, I was modeling a case where
Patients may or may not have had Legal Guardians. the way I solved it
was to create a "No legal guardian" record in the Guardians table, and
then using that as the parent of all Patients w/o a guardian. Then
you can just query the linked data source, set the guardianID to
whatever the fake guardian ID is for the people who don't have one,
and append that result to your final table.