no unique index found for the referenced field of primary table Results


Sponsored Links:



Hello,

I am trying a to build a slot booking database in which users will be able to book slots (ranging from 1-30) on a particular day for a specific site (location).

When trying to build the relationship between slot in tbl_available and slot in tbl_appointment i get the following error "No unique index found for the referenced field of the primary table"

the same error pops up when trying to build a relationship between site in tbl_available and site in tbl_appointment.

Can someone please explain to me why this happens?

I need both relationships to be 1 to many

thank you for your time




I have to create various tables using queries. The code for the first 2 tables is as follows


CREATE TABLE patient(
SSN char(9) NOT NULL,
PID char(8) NOT NULL,
Address char(60) NOT NULL,
BirthDate DATE NOT NULL,
Gender char(1) NOT NULL,
Name char(20) NOT NULL,
BloodType VARCHAR(2) NOT NULL,
PRIMARY KEY(SSN, PID))

and


CREATE TABLE operation(
OID char(8) NOT NULL,
Name char(20) NOT NULL,
Location char(60) NOT NULL,
Time char(10) NOT NULL,
PRIMARY KEY(OID))

Both of those queries where used to create their tables. My problem comes in when i try to create a relationship table.

CREATE TABLE undergoes(
PID char(8) NOT NULL,
OID char(8) NOT NULL,
FOREIGN KEY(PID) REFERENCES patient(PID),
FOREIGN KEY(OID) REFERENCES operation(OID))


The goal of that table is simply to reference which patient undergoes which surgery, however when i try to run the code i keep getting "No unique index found for the referenced field of the primary table". Any help is greatly appreciated, i have tried googling this problem and i see a lot of people have it but i am not understanding exactly what it means or how i can fix it.




I am trying to establish a one to many relationship between two tables similar to the one in Northwind where the Products table is related to the Order Details table in a one to many relationship via the ProductID field. I am getting a error report as follows: "No unique index found for the referenced field of the primary table"

Can anyone advise what this means & how to get around it?

Thank you




I've got this table with two particular fields in there (Year) and (Manufacturer).

I've just been having these two fields use a value list to select from for entering the correct value for (Year) and (Manufacturer).

However, I would like to set two new tables up for these two fields that way I have an unlimited amount of records I can enter.

So, I set each table up as follows.

(PK) ID--Autonumber
Manufacturer--Text

(PK) ID--Autonumber
Year--Text

I tried to tie these into my original (Inventory) table that contains these two fields.

How can I relate them so I can

a) enforce referential integrity

and

b) cascade update related fields.

??

It says the relationship type is Indeterminate.

When I try to create, it says no unique index found for the referenced field of the primary table.

Thanks




First of all hello everyone, I am new to these forums.

I am also new to access and trying to finish a project but have run into a little problem..

We are working on a project which requires us to create some tables, relationships, queries, etc.

I have created all the necessary table and occupied the appropriate tables with the approriate "customer information. Step 5 of our instructions says:

5. Create relationships between tables as follows:
a. Between Dependent and Rate tables using the DepID. (Always make sure the 1 and infinity (Many) symbols end up on the side of the relationship where they make sense. Study the tables to really understand what they represent. “Each Dependent can have Many Rates.” “Each Rate can be for only one Dependent.” This means the infinity symbol must end up on the Rate table side of the relationship. Always turn referential integrity on.
b. Between Company and Rate tables using InsID.
c. Between Employee and Select tables using EID.
d. Between the Select and Rate tables using both DepID and InsID primary key atttibutes. Note that Access can figure out that the attributes are in the tables in different orders.

I have done all this but the problem is when I double click the line between relationships to enable REFERENTIAL INTEGRITY I get an error:

"No unique index found for the referenced field of the primary table"

This happens no matter which relationship I try to enforce referential integrity on. I have searched the microsoft office 2007 help section to no avail.

Any help is appreciated!




Hello Access experts!

I have two tables. The master table uses the combination of Test Number and Original? as its primary key and the other table uses Test Number, Original?, and Position as its primary key.

I want to establish a one-to-many relationship by connection the Test Number and Original? together but I get the message: "No unique index found for the referenced field of the primary table." I've already tried connecting only the Test Number but it still doesn't seem to work. Does anyone have any ideas?

Thanks!




Hi all,

I'm creating a database and I'm having trouble with composite primary keys. I've read on a few forums that good databases should use composite primary keys over say an autonumber. And it does neaten up the database a bit, i.e. if a record is deleted there is no messy skipped numbers in the primary key (ID field). A simple example of my problem from my database is when I have two tables one that contains a list of raw materials and its relationship with the bill of materials (materials matched with products, a many-many table).

tbl_Raw_Materials
Material_Category
Material_Name (Primary Key)
Material_Colour (Primary Key)

tbl_Bill_of_Materials
Product_Code (Primary Key)
Material_Name (Primary Key)
Material_Colour (Primary Key)
Material_Length (Primary Key)
Material_Quantity

I have chosen these composite keys such that each record will contain a unique primary key. However, I have yet to work out how to induce referential integrity in these instances due the primary keys, and not recieve the message,

"No unique index found for the referenced field of the primary table."

Is there a way to achieve referential integrity while using composite primary keys? My main reason is to insure when a material is deleted from the database that it follows through to the bill of materials.

Cheers,

Raymas




Hi there, i'm trying to establish RI and cascade U/D to 2 tables in my DB.
I have ID ----> ID attributes both of which are primary keys, but i get a message 'No unique index found for the referenced field of the primary table', any suggestions would be great.




Hi, I am helping a friend reconfigure his database, and we would like to setup a way to delete records from the database once they are no longer needed. The database is currently setup with 3 tables (at least for this part).

Table 1 Items Sold
Contains fields
Autonumber (primary key)
Consignor number (linked with relationships to Consignor Information table and Volunteer table)
Item#
Description
PriceSold
DateSold
Volunteer?

Table 2 Consignor Information
Contains fields
Consignor # (primary key) (linked with relationships to Items Sold and Volunteer table)
Consignor First name
Consignor Last name

Table 3 Volunteer
Contains fields
Autonumber (primary key)
Consignor # (linked to Items sold and Consignor Information)

The 2 Relationships joining these tables are:

Relationship 1
Type: Indeterminate
From Volunteer Table To ItemsSold
Using the Consignor Number for the link

Relationship 2
Type: 1-to-Many
From Consignor Information table To ItemsSold table
Using Consigner Number for the link

I was trying to set it for Cascade delete (so that in a form only showing Consignor Information (from the Consignor Information table) you could delete ALL information related to that consigor. However when I try this I get 2 different errors, depending on the tables related:

Error 1 When trying to set Enforce Referential Integrity (cascade delete) for Relationship 1:
No unique index found for the referenced field of the primary table.

Error 2 When trying the same thing for Relationship 2:
Data in the table "ItemsSold" violates referential integrity Rules. For example, there maybe records to an employee in the related table, but no record for the employee in the primary table. Edit the database so that records in the primary table exist for all related records.

Can anyone help with this? Is there an easier way to do it? such as maybe a form where you can select the record and delete it?

Anyhelp would be great!!!


Related to this is another question dealing with the Volunteer Table.
for bookkeeping he has a form that steps you thru the process, on step 3 he has steps 3a, 3b, 3c.

3A: Open form (Volunteer Data Entry form) you enter in the consignors who are volunteering

3B: you have to open up a Query (Check Volunteer Marked Y) and change all of the Ns to Ys, which gets to be a VERY long process. Is there anyway to make them all Ys to begin with, without setting ALL people to Y, or to set it so that when you change the 1st N to a Y, it changes all of the Ns into Ys?

3C: just printing out the report.

Thanks again for any help you can give me on this




Hi all,
i have been developing a new database and the relationship are giving me some problems when i try to enforce intergrity rule .
iam developing a database for a small assisted living facility and here is what we want to achieve. be able to get information for parents/children/physician and location where they live. the facility has three different location and one central place.i have the following tables:
tables for patients,locations,physician,appointments, vehicles,guardians and medical info.i have also been getting a message, no unique index found for the referenced field of the primary table.
this is my second database am doing except this one has more tables than the previous one.
also i wanted to find out the easy way to change the auto subform made,how to change the view from dataview to single view.
i thank you all for your help




I have recently started a new project, on one of my tables i have a composite key for my primary key. However when i create a relationship using one of the fields in the primary key i can't "enforce referential integrity". Every time i try i get the message "No unique index found for the referenced field of the primary field"

can any one help

Mindy




I have two tables products and orders. Product table has product_id field and order table has product_id field.

Both of the product_id fields have duplicate records so i couldnt add a primary key to the product_id field in the products table..

I indexed both product_id fields in the 2 tables to allow duplicates.

Im trying to create a one-to-many relationship for the product_id in the product table and product_id in the order table.

When i try to create the relationship i get an error msg "No unique index found for the referenced field of the primary table".

How can i create the relationship if both fields contain duplicate rows??

Any help would be appreciated!!




I have inherited an Access 2007 database with two tables called “Incidents” and “CorrectiveActions”.
tblIncidents
IncidentID (Auto Numbered Primary Key)
IncidentNumber
Etc.
tblCorrectiveActions
IncidentNumber(Primary Key)
CorrectiveAction(PrimaryKey)
Etc.

There can me more than one CorrectiveAction for each IncidentNumber. (a one to Many relationship)

I want to enforce referential integrity between these two tables with a one to many relationship.
When I try to use the relationship editor I get an error message stating “No unique index found for the referenced field
of the primary table.” If I add the CorrectiveActionNumber to the CorrectiveActions side of the relationship editor, I get a notification that I haven’t picked a matching field on the “Incidents” side of the editor. There is no such field in the “Incidents” table.

Obviously I am inexperienced in Access. What am I doing wrong and how do I enforce referential integrity between these two tables with a one to many relationship? This DB is also accessed via an ASP app, so I would like to avoid adding additional fields etc to the tables if possible.




I'm trying to break down my Tables into smaller Tables with assigning & relating Tables by ID Fields. This whole relational concept I just can't fully get under my belt & is frustrating regardless how many times I watch videos on this concept. I know that if I could only understand everything & the 3-rules in creating relational tables I could go long ways ...?

Anyway, as I said I tend to relate my tables according to ID fields, but when I attempt to create queries on more then one "relted" table often times I get this message, "No unique index found for the referenced field of the primary table." What does it mean?




I am trying to make a simple database in Microsoft Access Professional Plus 2010, but it will not allow me to create a relationship properly because of. "No unique index found for the referenced field of the primary table". It will allow me to create a relationship between the two fields in question, but if I try to enforce referential integrity, it crashes and won't do it.... I have included screenshots of what is happening and would greatly appreciate any advice here, thanks in advance!! Attached Thumbnails     Reply With Quote 08-12-2012, 02:56 PM #2 ssanfu VIP Windows XP Access 2000 Join Date Sep 2010 Location Anchorage, Alaska, USA Posts 1,932 To referential integrity between two tables, one table (the primary table) must have unique values. It doesn't have to be the primary key, but the field must have unique values. The other field shouldn't have unique values.

There are other problems... You have all three tables with the PK as "Case ID". (BYW, You shouldn't use spaces in object names.) This is a one to one to one relationship. (I don't think that is what you want)
Each table should have different names for the PK fields to avoid confusion. It doesn't make sense to have the table "Tracer Log" have a PK of "Case ID". I would name the PKs:
Code: Table PK Field name --------------------------------- Contact "Contact_ID" Tracer Log "T_Log_ID" HUD "Hud_ID" If you wanted to referential integrity between Tables "HUD" and "Contact", with "Contact" being the primary table, you would add a field to the "HUD" table:
Name: "ContactID_FK"
Type: Long

Then drag the "Contact" table "Contact_ID" field to the "ContactID_FK" in the "HUD" table and set the referential integrity check box.




I was trying to do a many to one relationship and got this error. see attachment. Hotel Name to Hotel Name.

Error msg: "No unique index found for referenced field of the Primary table."

How do I fix it? Attached Thumbnails     Reply With Quote 12-08-2010, 02:51 PM #2 ConneXionLost Simulacrum Windows XP Access 2003 Join Date Jan 2010 Location Victoria, Canada Posts 291 "Hotel Name" is not the primary key in your Accomodation table.

Change the field in your Name-Address table to "Hotel ID"




Greetings

I'm sure this subject must have been addressed in the past but I would be grateful for some thoughts on it.

On the attached .pdf file diagram I have shown :-

1. a typical "top-down" tree structure (similar to a Bill of Materials but without the frills of including the number of a particular part that's required in the makeup of the item in the next level up)

2. How the structure is logically modelled

3. How the data would by physically held in a normalised table.


The problem I've encountered in ACCESS is how to set up the one-to-many relationship from the Parent to the Child. This can be done but if a try to force referential integrity (so that the "1" and the "∞" symbols are displayed at the ends of the relationship line) I am informed that this is not valid because "No unique index found for the referenced field in the primary table"

I am sure I've managed to do this in the distant past (not in ACCESS though) and I can't understand why, what is perfectly logical, cannot seem to be achieved or accepted in ACCESS.

Any thoughts would be gratefully received.




I am creating a table for Change orders. In my new table I have Survey No (Job No) and the respective Change orders. Usually all change orders range from 001 to 005. Instead of retyping my Survey No I wanted to have it look it up. When I do so, the lookup field is blank. I tried to create a relationship and when I do I get "No unique index found for referenced field of the primary table." What the heck is going on?? I don't understand why I cant lookup the data in another field. Very strange to me. Please help. The database is attached. Attached Files Test.zip (21.5 KB, 6 views) Reply With Quote 12-20-2011, 11:46 AM #2 dblife Advanced Beginner Windows XP Access 2003 Join Date Nov 2011 Location South Shields, UK Posts 88 if you are trying to make a field the primary key or enforce referential integrity after you have put values in the table, access will give you this error.
You must have duplicate values in the field you are trying to relate.




Hi

Background and goals
I'm designing a database for a manufacturing company wanting to define which items can be produced by witch tools.
The database will be replicated on 3 production sites (There is no fast network connections)

A tool can have have several insertsAn insert can only be used in one toolAn insert can produce several different items and an item can be produced on several tools.
Tables

1 tool table with a tool id as primary key1 insert table with the fields tool id and insert id defined as a primary key1 item table with an item id as primary key1 item-insert table having the fields tools, inserts and items as primary key.
Relation

1-n between tool and insert1-n between item and insert item
See screenshot underneath


Problem
I have to set a 1-n relation based on the fields tool and insert of the table insert and items-insert.
Access just refuse to do so and send me the followinf error message :
"No unique index found for referenced field of primary table. "

I've considered the Article from Microsoft concerning relation with multiple field primary key. http://support.microsoft.com/kb/155514
I've tried differents set of order of the fields and indexes but it did not solve my problem. (I've succeed on linking two multiple-field primary key tables with a 1-1 relation)

Work arround
I've redesigned the database using a single field with a random generated integer primary key on the table insert. (See below)

and I set on the field tool and insert of the table inserts an index checking unicity.
It works but, I'm concerned about possible conflicts while adding records on the different sites and then synchronise them. I'm expecting about one million records on the inserts/item table and I will be able to synchronise after each sites adds 30-50000 records.
I don't have this issue on the original design as each sites can only works on their own tools.

Questions :
Can somebody tell me why I can't create in Access the original design?
Is there another way to overcome that problem? (I would like to avoid any VBA based solution)?

Many thanks in advance

David