A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Referential Integrity Q



 
 
Thread Tools Display Modes
  #1  
Old May 4th, 2010, 08:11 PM posted to microsoft.public.access.tablesdbdesign
wallymeister
external usenet poster
 
Posts: 26
Default Referential Integrity Q

Is it Possible to change a common relationship between two tables to Force
Referential Integrity after the data has been entered.

I have two tables joined lie this,

Models Table; ModelID (PK), PartsID (LI) Indexed-Yes (No Duplicates)

Parts Table; PID (PK), PartsID (LI) Indexed-Yes (Duplicates OK)

They are linked by PartsID and the primary table is Models Table. There are
no duplicates in PartsID in Models Table, but when I try to Enforce Ref. Int
I get the mesaage "No index found for the referenced field of the primary
table."

Any light shed would be very helpful.
Thanks,
Wally
  #2  
Old May 4th, 2010, 08:53 PM posted to microsoft.public.access.tablesdbdesign
John W. Vinson
external usenet poster
 
Posts: 18,261
Default Referential Integrity Q

On Tue, 4 May 2010 12:11:01 -0700, wallymeister
wrote:

Is it Possible to change a common relationship between two tables to Force
Referential Integrity after the data has been entered.


Sure, just so long as there are no existing records which would cause a
problem.

I have two tables joined lie this,

Models Table; ModelID (PK), PartsID (LI) Indexed-Yes (No Duplicates)

Parts Table; PID (PK), PartsID (LI) Indexed-Yes (Duplicates OK)

They are linked by PartsID and the primary table is Models Table. There are
no duplicates in PartsID in Models Table, but when I try to Enforce Ref. Int
I get the mesaage "No index found for the referenced field of the primary
table."


It sounds like you're trying to build the link backward. You have PartsID
uniquely indexed in the Models table, rather than in the Parts table. For that
matter, why does the Parts table have both a PID (primary key) and also a
PartsID? Might a given PartsID value appear repeatedly in Parts, with
different PID? If not, why two fields?
--

John W. Vinson [MVP]
  #3  
Old May 4th, 2010, 09:22 PM posted to microsoft.public.access.tablesdbdesign
KARL DEWEY
external usenet poster
 
Posts: 10,767
Default Referential Integrity Q

You got to change to this --
Parts Table; PartsID (PK)

--
Build a little, test a little.


"wallymeister" wrote:

Is it Possible to change a common relationship between two tables to Force
Referential Integrity after the data has been entered.

I have two tables joined lie this,

Models Table; ModelID (PK), PartsID (LI) Indexed-Yes (No Duplicates)

Parts Table; PID (PK), PartsID (LI) Indexed-Yes (Duplicates OK)

They are linked by PartsID and the primary table is Models Table. There are
no duplicates in PartsID in Models Table, but when I try to Enforce Ref. Int
I get the mesaage "No index found for the referenced field of the primary
table."

Any light shed would be very helpful.
Thanks,
Wally

  #4  
Old May 5th, 2010, 12:08 PM posted to microsoft.public.access.tablesdbdesign
wallymeister
external usenet poster
 
Posts: 26
Default Referential Integrity Q

Thanks Karl & John,
I got it now, as far as the extra PID, I just added that while playing
around with a copy of the real thing. It will not be there in the finished
product. Anyway thanks again for stilling my fried brain a little to get me
thinking correctly.
Wally

"John W. Vinson" wrote:

On Tue, 4 May 2010 12:11:01 -0700, wallymeister
wrote:

Is it Possible to change a common relationship between two tables to Force
Referential Integrity after the data has been entered.


Sure, just so long as there are no existing records which would cause a
problem.

I have two tables joined lie this,

Models Table; ModelID (PK), PartsID (LI) Indexed-Yes (No Duplicates)

Parts Table; PID (PK), PartsID (LI) Indexed-Yes (Duplicates OK)

They are linked by PartsID and the primary table is Models Table. There are
no duplicates in PartsID in Models Table, but when I try to Enforce Ref. Int
I get the mesaage "No index found for the referenced field of the primary
table."


It sounds like you're trying to build the link backward. You have PartsID
uniquely indexed in the Models table, rather than in the Parts table. For that
matter, why does the Parts table have both a PID (primary key) and also a
PartsID? Might a given PartsID value appear repeatedly in Parts, with
different PID? If not, why two fields?
--

John W. Vinson [MVP]
.

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 07:32 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.