Prev: Multiple subtables
Next: #Name?
From: wallymeister on 4 May 2010 15:11
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
From: John W. Vinson on 4 May 2010 15:53
On Tue, 4 May 2010 12:11:01 -0700, wallymeister
<wallymeister(a)discussions.microsoft.com> 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]
From: KARL DEWEY on 4 May 2010 16:22
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
From: wallymeister on 5 May 2010 07:08
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
> <wallymeister(a)discussions.microsoft.com> 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]
> .
>
 | 
Pages: 1
Prev: Multiple subtables
Next: #Name?