Two Primary Keys on one Table

[KatGraham]

Working in Access 2000.

My first time with 2 primary keys...

I have setup two primary keys on 1 table.

Both fields are set with:
Required: No, Indexed: Yes (Duplicates OK), Unicode Expression: Yes.

I and am trying to set the relationship between this table and another table in the database.

I get an error message when I try to Enforce Referential Integrity. The message is: "No unique index found for the referenced field of the primary table".

Help!

 

[MattThat]

Required: No, Indexed: Yes (Duplicates OK), Unicode Expression: Yes...

...Gives us all the information we need to crack this problem. A primery Key is a unique handle by which we can identify the record. Think of it like your National Insurance number which is used to identify references to you.

Set the value in Indexed to: Yes (No Duplicates) and the problem will vanish.

 

[KatGraham]

Here is an example of what I am talking about.

Table 1:
ServiceCode Description Amounts
101 Internal 100.00
101 External 250.00

I set two primary keys: ServiceCode and Descripton

Table 2:
ServiceCode Description Units
101 Internal 1.00
101 External 4.00

How do I get REFERENTIAL INTEGRITY to work??

 

[MattThat]

I can see vast ammounts of data repetition in those tables. I think perhaps the problem is the disign of your relationships (ERD).

If I say "Normalisation" would you follow what I was saying? If so I would re-normalise bacause something went astray.

If not then I would have to say you need to sort the fields and tables until no data is repeated (only unique IDs should be repeated for reference).

To answer question two: look at answer one. To get the DB to accept your relationship and enforce RI you must use unique values.

sorry mate there is no way round this one.

 

[jfrost10]

Hey Kat,

why do the Units and Amounts have to be in sepereate tables?

Jack

 

[KatGraham]

The tables are really Excel spreadsheets that are imported into the database. Table 1 = My own table to show all allowable Services (example) with amount per service. The other table (imported) is the detail activity. I need to then query the two to produce (unit x amount).

 

[jfrost10]

Service
ID Code Desc Units
1 101 Internal 1.0
2 101 External 4.0

Detail
ID ServiceID Amount
1 1 100.00
2 1 250.00
3 2 150.00

k, here's how I would set up your tables:
The service table holds data about what TYPE of service you do. The Code and Desc are entered by the users (system doesn't really care). But the ID is the UNIQUE identifier for the records.

Now the second table is your Detail table. Each of those records has its own ID as well. It also has a ServiceID column which is a foreign key to the Service table (which reduces the need to store the Code and Desc twice in different spots). The last table is the Amounts, holding the amount info.

This will clean up the tables, and you won't have teh referential integrity issues that you're running into.

hth

Jack

 

[KornGeek]

While jfrost10's idea is very clean and will work well, I understand that you may have difficulty implementing it if you already have table structures coming from the spreadsheets.

If you have to maintain the two-key system, make sure that the joins between the tables are on both fields. Also, make sure that you have actually designated them as the primary key.

I have a number of tables that use two (or more) primary keys. If you make sure that the joins use all of the keys, it should work.

Good luck.

 

[KatGraham]

The problem is:

Service = I build = OK
ID Code Desc Units
1 101 Internal 1.0
2 101 External 4.0

Detail
ID ServiceID Amount
1 1 100.00
2 1 250.00
3 2 150.00

The problem with the Detail table is that this table is imported from Excel. I can't change the Service ID. The Service ID is 101 and 102 etc.

 

[jfrost10]

Ah yes, the xHell factor.

Question: you mention that the Service ID is 101, 102, etc., but in your examples you have
101 Internal
101 External

So the Service ID's are not unique to themselves, right (hence why you were asking about he dual keys?)?

Question: Are you storing the details long term, or are they just used to calculate the amount x units for a report that Access generates? If so, then you may not even NEED to set up the referential integrity (if you are storing them in the database, just curious how you'd identify them from different imports from different xHell spreadsheets/times/etc.)

you could just import the data as is, run a processing script, and output the report. In fact, that would probably be the best way to do it, because then you could create a table that will be permanent that will hold all the detail data AND be normalized.

But I'm getting ahead of myself here (always my head in the clouds...).


Jack