one to one with referential integrity = error?

[nedstar1]

Hi friends,

Before I have to toss on the nomex suit, I am, yes, using a table in a one to one relationship. It's beacause for ean record in tblJobs, I need to store a little over 400 pieces of information, so I split it between two tables.

Well, yesterday, I turned my kiddies loose on the live database, and tonight I am, of course, fixing it. Nothing like a little real world testing, eh?

I have an issue - If I turn on referential integrity in the relationship between tblJobs and tblJobVarious, no problem. But if I turn on "cascade update related fields" it errors, saying I have error 3409 -
"
Invalid field definition <name> in definition of index or relationship. (Error 3409)
Possible causes:

The field name you have specified might be misspelled. Check the spelling of the field name.
The field type you are using is a Memo or OLE Object, which cannot be indexed.
"

So the field name is JobID in both tables. I have a PK in every table, and a FK to tblJobs.JobID in all relevant tables. So I';m reasonably sure I'm spelling it right.

Neither field is a Memo or OLE field. Both are number, long integer.

Here's my issue - with referential integrity turned on, if a user enters data into a new record in my frmJobs, it will not allow them to save the record, as there is no related record in tblJobVarious.

I suppose my ultimate question is, would referential integrity auto-create that record, or do I need to code it somehow in the frmJobs events?

And, the requisite follow-up, how would I resolve this problem as quickly as possible?

TIA, friends, always a wealth of knowledge here.

Regards,
Nedstar1

 

[nedstar1]

Thanks to PHV and Mike for their help in my other thread.

Revised question - can/how do I create an associated record if one is created in one table in a one-to-one with referential integrity on, automatically?

Part of me is sure this is simple, and I'm racing you all to the answer with my tomes piled around me, Tek-Tips on the screen.

I really need to get this issue buttoned down - anything you could share is a wonderful and gratefully appreciated nudge in that direction.

TIA

Regards,
Nedstar1

 

[PHV]

Perhaps a DoCmd.RunSQL "INSERT INTO ..." in the AfterInsert event procedure of the form ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[nedstar1]

Thanks for the push in the right direction. Now if only I knew enough SQL to write it up. Think if I built it as a query, it would get me pretty close?

Off to try . . .something.

Thanks again.

Regards,
Nedstar1

 

[PHV]

if only I knew enough SQL
Search your local drives for files named JET*.CHM

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[nedstar1]

References I never knew I had . . .

Makes me feel kind of silly to have all of these books when these small help files do the trick.

Back at it - many thanks for the tip.

Nedstar1

 

[BNPMike]

The first thing to realise is you don't need referential integrity, or relationships. So if they ever cause you difficulties then drop them.

Probably the most likely cause of your problem is you are adding the wrong record first. If you add the referenced record first you should be OK. Obviously, don't try and tell Jet that both records reference each other otherwise you'll never get out of the deadly embrace.

 

[DanJR]

The first thing to realise is you don't need referential integrity, or relationships. So if they ever cause you difficulties then drop them.

In that case, the second thing to realise is that you don't need any intergrity contraints at all, you don't need to consider the relational model and you certainly don't need to use a DBMS. Just use an ASCII text file and if you need to improve performance then you can build your own B+ Tree index. Don't bother considering what constitutes a legal record (tuple) instance either - cause you wont be checking it. Let's just rely on the applications to check it for us.

The last i saw, E. F. Codd incorporated "a collection of general integrity rules" into the definition of the relational model. I'm pretty sure that includes foreign key constriants!

----
I've never come across this error before. What happens if you create the two 'tables' again (just adding the key fields) and select the cascade update option when no data is in the tables.

I have had other problems with one to one 'relationships' and referential integrity with MS Access before. For example i've taken a relational model and implemented it in MySQl, fireBird, MS SQL Server, Oracle and its worked fine, but MS Access get confused with cascading deletes (sorry, i can't remember what the exact problem was but it didn't generate an error).

cheers,
Dan

 

[BNPMike]

Mr Codd would have recommended using referential integrity or indeed any other method of declaring rules in the database rather than in the applications. However I sense that people sometimes use these in Access because they think they are the way you connect entities together. Their motives may therefore not be correct. I find it is better if you get your application working correctly before you firm up on the constraints.