Need some help with Unique Index Error

  • Thread starter Thread starter Joe C
  • Start date Start date
J

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!
 
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
 
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?

Michel said:
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 said:
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!
Click to expand...
Click to expand...
 
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


Joe C said:
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?

Michel said:
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 said:
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!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top