Need some help with Unique Index Error

[Joe C]

Trying to understand why I get "no unique index" message in my database
when I try to create a cascading relationship between two of my tables.
Here are the tables involved:

3 Tables joined to each other in the following manners:

Table 1: Establishment: (1 PK field Named "Permit_Number": [Long
Integer])

Table 2: Establishment_Inspection: (Compund PK: "Permit_Number" [Long
Integer], "Inspection_Date" [Date/Time(Medium)]

Table 3: Establishment Violation: (Compund PK; "Permit_Number" [Long
Integer], "Violation_Date" [Date/Time(Medium)], "Violation_Number"
[Long Integer]


Table 1 has a 1-many Join to table 2 via a Permit#.
Table 2 has a 1-1 Join to Table 3 via Permit_Number, Inspection_Date
and Violation_Number (data type the same in each table)

Every time I try to create a cascading query I get a message stating
there is no unique index found in Table 2 (My Primary table). However,
if I change the structure of table 2 so that it has the same primary
key structure to table 3 then the cascade function works with no error
messages.

Very confounding. Thanks in advance for your help!

 

[Michel Walsh]

Hi,



A 1-1 relation is, in fact, a 1-many relation where the many is, AT MOST, 1
(can be 0, and 1).

So, start a drag from table3 and drop over table2 (not the reverse) so
table3 is on the 1 side and table2 is on the many side... not the reverse
(from table2 dropping on table3).


Hoping it may help,
Vanderghast, Access MVP

 

[Joe C]

Michael

Thanks for the reply. The problem with making Table 3 primary is that
Table 2 has records that are not included in table 3. I don't know if
this fact in itself violates referential integrity? My understanding
is that Table 3 should have all related records to Table 2 but not
neccessarily exclusively all records from Table 2??

Any other thoughts?

Hi,



A 1-1 relation is, in fact, a 1-many relation where the many is, AT MOST, 1
(can be 0, and 1).

So, start a drag from table3 and drop over table2 (not the reverse) so
table3 is on the 1 side and table2 is on the many side... not the reverse
(from table2 dropping on table3).


Hoping it may help,
Vanderghast, Access MVP

Trying to understand why I get "no unique index" message in my database
when I try to create a cascading relationship between two of my tables.
Here are the tables involved:

3 Tables joined to each other in the following manners:

Table 1: Establishment: (1 PK field Named "Permit_Number": [Long
Integer])

Table 2: Establishment_Inspection: (Compund PK: "Permit_Number" [Long
Integer], "Inspection_Date" [Date/Time(Medium)]

Table 3: Establishment Violation: (Compund PK; "Permit_Number" [Long
Integer], "Violation_Date" [Date/Time(Medium)], "Violation_Number"
[Long Integer]


Table 1 has a 1-many Join to table 2 via a Permit#.
Table 2 has a 1-1 Join to Table 3 via Permit_Number, Inspection_Date
and Violation_Number (data type the same in each table)

Every time I try to create a cascading query I get a message stating
there is no unique index found in Table 2 (My Primary table). However,
if I change the structure of table 2 so that it has the same primary
key structure to table 3 then the cascade function works with no error
messages.

Very confounding. Thanks in advance for your help!

 

[Michel Walsh]

Hi,


Then, in this case, you include the index, unique, on table2, and
(optionally) remove it from table3 (or keep it, there should be no harm
done). Indeed, a relation 1-1 INCLUDES also the 1-0 case (ie, in your
case, be in table2 but not, 0 record matching, in table3). That is why it
*IS* important to start with the correct table, when you define a 1-1
relation; easier, in fact, to see it as a 1-many relation! (where many = 0
or 1, but *no more* than just one).


Hoping it may help,
Vanderghast, Access MVP

Michael

Thanks for the reply. The problem with making Table 3 primary is that
Table 2 has records that are not included in table 3. I don't know if
this fact in itself violates referential integrity? My understanding
is that Table 3 should have all related records to Table 2 but not
neccessarily exclusively all records from Table 2??

Any other thoughts?

Hi,



A 1-1 relation is, in fact, a 1-many relation where the many is, AT MOST,
1
(can be 0, and 1).

So, start a drag from table3 and drop over table2 (not the reverse) so
table3 is on the 1 side and table2 is on the many side... not the
reverse
(from table2 dropping on table3).


Hoping it may help,
Vanderghast, Access MVP

Trying to understand why I get "no unique index" message in my database
when I try to create a cascading relationship between two of my tables.
Here are the tables involved:

3 Tables joined to each other in the following manners:

Table 1: Establishment: (1 PK field Named "Permit_Number": [Long
Integer])

Table 2: Establishment_Inspection: (Compund PK: "Permit_Number" [Long
Integer], "Inspection_Date" [Date/Time(Medium)]

Table 3: Establishment Violation: (Compund PK; "Permit_Number" [Long
Integer], "Violation_Date" [Date/Time(Medium)], "Violation_Number"
[Long Integer]


Table 1 has a 1-many Join to table 2 via a Permit#.
Table 2 has a 1-1 Join to Table 3 via Permit_Number, Inspection_Date
and Violation_Number (data type the same in each table)

Every time I try to create a cascading query I get a message stating
there is no unique index found in Table 2 (My Primary table). However,
if I change the structure of table 2 so that it has the same primary
key structure to table 3 then the cascade function works with no error
messages.

Very confounding. Thanks in advance for your help!