Re: Referential Integrity on relationship with Multiple fields

From: Allen Browne (AllenBrowne_at_SeeSig.Invalid)
Date: 03/18/04 

Date: Fri, 19 Mar 2004 01:56:15 +0800

Just tested that in 2 steps, and no problem here.

Deleted my relation.
Added an entry to Table1 just to be sure.
Created the 2-field relation without RI.
Re-opened the Relationships window, double-clicked the relation, and checked
the RI box.

Wonder what's different?

(Will check back tomorrow: it's 2am here.) 

-- 
Allen Browne - Microsoft MVP.  Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Peter Johal" <pjohal02@hotmail.com> wrote in message
news:77E0E8BC-9B85-4A0F-AB3E-72132A3141CB@microsoft.com...
>
> I'm using the same jet engine. And tried to create the relation on an
access 97 database, but again I got the same result.
>
> The relationship I'm trying to create is exactly as you discribe it. But I
want to emphasize that I am able to create the relationship. I am just
unable to turn on the option to enforce referential integrity. I do it in
two steps: first I create the relationship and save. Then I try to turn on
referential integrity and I get the error message.
>
> Peter
>
>      ----- Allen Browne wrote: -----
>
>      Yes: created in A2003.
>      Just tested a pair of 30-char fields.
>
>      Don't know if it makes a difference, but I am using Jet 4 SP8, i.e.
>      msjet40.dll is version 4.0.8015.
>
>      You are creating the relation on the combination of the 2 fields?
i.e. in
>      the Edit relations dialog, you have:
>          Table/Query            Related Table/Query
>          Table1                    Table2
>          F1                            F1
>          F2                            F2
>      where Table1 is the one that has F1 + F2 as primary key.
>
>
>      "Peter Johal" <anonymous@discussions.microsoft.com> wrote in message
>      news:7C5FD2B1-FAA8-421C-A9C9-C521E01DFDBB@microsoft.com...
>      >> I have two text fields length 30 in both tables. Data type and
size match.
>      And there are no hidden indexes, I used your count statement: I a
count of
>      1.
>      >> I created two dummy tables from scratch and tried to enforce
referential
>      integrety on again on a relationship with two fields, but again with
the
>      same result.
>      >> Then I changed the datatype on the first field on both tables to
>      autonumber/ numeric. But again I got the same result.
>      >> Are you using access 2003?
>      >> Peter
>      >>      ----- Allen Browne wrote: -----
>      >>      Just tested here, Peter, and the relationship was created
fine.
>      >      The example I tested used a Number (Long) + Text (20-char) in
both
>      tables.
>      >>      Make sure your field pair match on data type and size in both
tables.
>      >      Remove any spurious indexes on the table, including any hidden
>      indexes.
>      >      You may need to test this programmatically:
>      >          ? CurrentDb().TableDefs("YourPrimaryTable").Indexes.Count
>      >>      "Peter Johal" <anonymous@discussions.microsoft.com> wrote in
message
>      >      news:973C56DA-A7FB-4B8A-8418-6FE7B50F4CCA@microsoft.com...
>      >> I cannot turn on referential integrity on a relationship
>      (parent-child)
>      >      with two field. Access gives the error message (translated
from
>      dutch):
>      >>>     No unique index has been found on field of primary table that
>      is
>      >      references. (Error 3609)
>      >>> However, the primairy key of the primary table consist of
>      precisely the
>      >      two field in the relationship. So there is a unique index on
those
>      two
>      >      fields.
>      >>> I'm using access 2003. Is this an error or a limition of access?
>      What can
>      >      I do?
>      >>> Peter

Relevant Pages

  • Re: Referential Integrity on relationship with Multiple fields
    ... Just tested here, Peter, and the relationship was created fine. ... > I cannot turn on referential integrity on a relationship ... Access gives the error message: ... No unique index has been found on field of primary table that is ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Referential Integrity on relationship with Multiple fields
    ... "Peter Johal" wrote in message ... > I cannot turn on referential integrity on a relationship ... references. ... So there is a unique index on those ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Referential Integrity on relationship with Multiple fields
    ... And tried to create the relation on an access 97 database, but again I got the same result. ... Peter ... > Remove any spurious indexes on the table, ... >> I cannot turn on referential integrity on a relationship ...
    (microsoft.public.access.tablesdbdesign)
  • Referential Integrity on relationship with Multiple fields
    ... I cannot turn on referential integrity on a relationship with two field. ... Access gives the error message (translated from dutch): ... No unique index has been found on field of primary table that is references. ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Get Connected Crashes Computer
    ... Susan, have you solved your problem, in that case, what did you do? ... "Peter B" wrote in message ... Unfortunately I cannot reproduce the problem on ... >>>that error message is very useful. ...
    (microsoft.public.pocketpc.activesync)