Database: two primary keys in the same table

Irish Stones

I have a database made of several tables and each tables contains up to 7 fields.
I needed to re-sort one of these tables who had two primary keys. In order to edit this table I had to disable the two primary keys. Now the table and its fields are alright, but I want to restore the two primary keys.
I'm using MDB Plus Viewer and it doesn't allow me to do that. I also tried with MS Access, with no results. The message is that it can't be done because of something about duplicated data or similar.
Is there a way to work this problem around?
I'm not very familiar with these applications, probably there's something that I don't know.
Thanks!

bonzodog2

You should only have one primary key. Maybe you've added a row that has a duplicate of this field?

Irish Stones

No, the original database, the one that I wanted to edit, had a table with two primary keys. There are 5 fields, the first is the incremental ID, not primary, then there are four more fields, the first two are both primary keys, when I open the structure of the database I see two key logos next to the name of the fields.
I disabled the two keys, so that I was able to work on those fields, at the end of the work I wanted to restore the two keys but it was no longer possible.

If you use Access and create a new table in a database and open the structure of that table you see the fields. Right click on one of the fields and add the key, but if you select two fields, press CTRL and then right click, you can add keys to both fields.
Who created the database must have done something like that, I am not that good...

leeroybrown

bonzodog2 wrote: »

You should only have one primary key. Maybe you've added a row that has a duplicate of this field?

The original database must have had a multi-field primary key. The two fields combined would provide uniqueness.

Irish Stones wrote: »

If you use Access and create a new table in a database and open the structure of that table you see the fields. Right click on one of the fields and add the key, but if you select two fields, press CTRL and then right click, you can add keys to both fields.
Who created the database must have done something like that, I am not that good...

In MDB Plus Viewer you should be able to use "create index" to make a multi-field primary key. However, if your changes to either row has broken the uniqueness of the key it probably won't work.

maki

From the sound of it, you already have a surrogate key (the incremental ID). Why not use that as the primary key instead of creating a compound key?
You've already needed to edit the primary key, which is bad practice as they should ideally be static.

Irish Stones

leeroybrown wrote: »

The original database must have had a multi-field primary key. The two fields combined would provide uniqueness.

In MDB Plus Viewer you should be able to use "create index" to make a multi-field primary key. However, if your changes to either row has broken the uniqueness of the key it probably won't work.

So, I opened the database with MDB Plus Viewer, I opened the table where I need the two keys, I clicked on Indexes and a new window opens, there is a list of my tables, the table which should have two keys has two indexes that are what I need to turn into primary keys. The "unique" and "primary key" columns say "FALSE".
So I click on "Add Index" and the window shows the fields of this table, I tick the two fields, I select "Create Index" and "Unique" then I click on "Create Index", but the message that appears says:
"Error creating index" because I tried duplicate the value in the index or the primary key. I should modify the data in in the fields that contain duplicated data, remove or redefine the index to allow the entry of duplicated data and then try again "Create Unique Index Field1xField2 on Table (Field1, Field2) with Disallow Null".
Did I make anything wrong?

leeroybrown

I never use Access or MDB Plus Viewer and know nothing about your schema so I can't really help you much more but...

It sounds like you now have duplicate data in the old primary key fields and they no longer provide unique references for ever row. If you want/need to have this primary key then you need to go back to having these fields providing unique data. Otherwise you'll have to do figure out something else.

Irish Stones

maki wrote: »

From the sound of it, you already have a surrogate key (the incremental ID). Why not use that as the primary key instead of creating a compound key?
You've already needed to edit the primary key, which is bad practice as they should ideally be static.

The database was a mess and needed a sort of all the entries. So I did it, it took me days, and to do that I had to disable the two existing keys. When it was time to restore the keys this problem arose.