Advertisement
If you have a new account but are having problems posting or verifying your account, please email us on hello@boards.ie for help. Thanks :)
Hello all! Please ensure that you are posting a new thread or question in the appropriate forum. The Feedback forum is overwhelmed with questions that are having to be moved elsewhere. If you need help to verify your account contact hello@boards.ie

Database: two primary keys in the same table

Options
  • 04-08-2014 11:01am
    #1
    Irish Stones
    Registered Users Posts: 2,307 ✭✭✭


    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!


Comments

  • Options
    #2
    bonzodog2
    Registered Users Posts: 6,220 ✭✭✭bonzodog2


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


  • Options
    #3
    Irish Stones
    Registered Users Posts: 2,307 ✭✭✭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...


  • Options
    #4
    leeroybrown
    Closed Accounts Posts: 7,563 ✭✭✭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.


  • Options
    #5
    maki
    Registered Users Posts: 772 ✭✭✭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.


  • Options
    #6
    Irish Stones
    Registered Users Posts: 2,307 ✭✭✭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?


  • Advertisement
  • Options
    #7
    leeroybrown
    Closed Accounts Posts: 7,563 ✭✭✭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.


  • Options
    #8
    Irish Stones
    Registered Users Posts: 2,307 ✭✭✭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.


Advertisement