Relationship Many-to-Many (enforcing referential integrety)

[Bigtonyicu@gmail.com]

Hi Everyone,

I'm building a database to track my company's Software licensing. I'm having an issue when I try to enforce referential integrety.

Original I had 3 tables involved in this issue.

Table 1 (Software)

Manufacturer	Software (PK)
Adobe	Acrobat Pro
Microsoft	Office Pro
Microsoft	Project

Table 2 (Software_Version)

ID(PK)	Software*	Version
1	Acrobat Pro	10
2	Office Pro	2007
3	Office Pro	2002
4	Project	2002

*Software is linked to "table 1"'s Software


Table 3 (Assigned_Software)

User	Software	Upgraded Version*	Original Version*
John Doe	Office Pro	2007	2002
Jane Doe	Acrobat Pro	10	8

*Linked to "table 2"'s Version
(sorry Primary Key not displayed auto number field)


When I tried to set up a relation ship to "Table 2"'s Version I got an error saying that Table 2 doesn't contain a primary key. After doing some digging I found out I was trying to to a many-to-many relationship and this would require a junction table so I added a fourth table

Table 4 (Version)

Version (PK)

10

2002

2007

I then tried creating a relationship between "table 4"'s "Version" and "table 3"'s "Upgraded Version" and "Original Version"... it works!!!
I then tried to enable "Enforce referential Integrity" and received the following error

"Microsoft Office access can't enforce referential integrity for this relationship.

Make sure the fields you drag are primary key fields or uniquely indexed and the unique index or primary key is correctly set.
If you want to create a relationship without following the rules of referential integrity, clear the enforce referential integrity check box"

Help, how do I fix this???



Anthony

[ssanfu]

In your Table 2, you set the field "Software" as the PK, but you have two "Office Pro" records - the PK must be unique.

I would suggest trying this structure (this is a first pass):


Software (Table 1)
--------------
Software_ID (Autonumber - PK)
Manufacturer (Text)
Software_Name (Text)


Versions (Table 2) (NOTE: Version {singular} is a reserved word)
----------------
Version_ID (Autonumber - PK)
WhatVersion (Text)


SoftwareVersion (Table 3 - Junction table)
----------------
Software_Ver_ID (Autonumber - PK)
SoftwareID_FK (Number - Long)
VersionID_FK (Number - Long)


Employees (Table 4)
-----------------
Emp_ID (Autonumber - PK)
Emp_FName (Text)
Emp_LName (Text)


AssignedSoftware (Table 5 - Junction table)
-------------------------
Assigned_ID (Autonumber - PK)
EmpID_FK (Number - Long)
SoftwareVerID_FK (Number - Long) (link to Software_Version table)
InstallDate (Date/Time)
Org_Upgrade (Text) (option: Original or Upgrade)



Tables 1 and 2 linked to Table 3
Tables 3 and 4 linked to Table 5