?
Solved

SQL code for Access database adding multipe foreign keys to a table

Posted on 2013-11-07
5
Medium Priority
?
491 Views
Last Modified: 2013-11-07
I am trying to code a table in Access that has multipe foreign keys. This is what I have so far:

CREATE TABLE Activities
(
[actID] Counter NOT NULL,
[stoID] Int NOT NULL, 
[TaskOrderID] int NOT NULL,
[actdesc] Varchar(100) NOT NULL,
[acttypeID] Int NOT NULL,
PRIMARY KEY (actID, stoID, TaskOrderID),
FOREIGN KEY (stoID) REFERENCES SubTaskOrders (stoID),
FOREIGN KEY (TaskOrderID) REFERENCES TaskOrders (TaskOrderId),
FOREIGN KEY (acttypeid) REFERENCES ActivityType (acttypeid)
);

Open in new window


I am getting the error message: “No unique index found for the referenced field of the primary table.”

I know I must be coding this wrong, but I am also not sure what this error message means, or why I am getting it.

I can't wait to hear what I am doing wrong!

Thank you in advance!
0
Comment
Question by:Megin
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 85
ID: 39632135
That means one or more of your referenced tables (SubTaskOrders, TaskOrder or ActivityType) do not have a Unique Constraint on them, or more specifically on the field that you're referencing from those tables. For example. SubTaskOrders.stoID may not be defined as a "Unique Constraint".
0
 

Author Comment

by:Megin
ID: 39632144
So, making something a primary key is not enough to satisfy that?
0
 
LVL 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 2000 total points
ID: 39632153
Not necessarily.

Also, be aware I'm referring to the "child" tables, and not your Activities table. In other words, the table named TaskOrders must have a Unique constraint, and that constraint must include the field you're referencing as a foreign key.
0
 

Author Comment

by:Megin
ID: 39632157
Also, I am looking for the code I have to alter a table, because I am assuming that is the only way I am going to be able to change a field to for this Unique Constraint. But the examples are for adding keys, deleting columns, etc. What would be proper code be to add this particular thing to the table?
0
 

Author Closing Comment

by:Megin
ID: 39632211
I just went in and figured out the Alter table syntax to add UNIQUE to all my primary keys. It looks like that worked.

Thank you for the help!
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question