Results 1 to 8 of 8
  1. #1
    Join Date
    Jun 2007
    Posts
    28

    Unanswered: duplicate entry in a primary key field

    Hi everybody couldn't get through with saving my data on the table with two primary keys...

    my table structure is this

    pubid int Unchecked (primary key)
    pub char(1) Unchecked
    publ char(1) Unchecked
    pubcode char(2) Unchecked (primary key)

    a sample data is here

    pubid pub publ pubcode

    1 a b ab
    1 b b bb
    2 a b ab
    2 b b bb


    when i save this table modifying the pubid and pubcode as primary keys the following error displays...

    Unable to create index 'PK_PUBS3'.
    CREATE UNIQUE INDEX terminated because a duplicate key was found for index ID 1. Most significant primary key is '51'.
    Could not create constraint. See previous errors.
    The statement has been terminated.


    what i understand is that on the primary key duplicates are not allowed how could i allow it?

    thanks

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Alex - you could probably do with a brush up on relational database design & theory.
    http://r937.com/relational.html

    1) You can't have two primary keys - there can only be one. Most likely you have a composite primary key.
    2) The whole point of a primary key is that it is unique - this is pretty well the central tenet of a primary key.

    What columns in your table uniquely identify a row? It looks like it could be a combination of pubid and pubcode but please let us know.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Nov 2002
    Posts
    272
    You can have exactly 1 primary key. If I understand correctly, the PK is now a composite key on 2 columns, pubid and pubcode. There are no duplicate combinations of values in that combination of columns, even though both columns individually do contain duplicate values.

  4. #4
    Join Date
    Jun 2007
    Posts
    28
    there is no column that uniquely identify a row actually the table is a junction table for many to many relationship pubid is the foreign key for table1 and pubcode for table3 I'm normalizing the database so i created this juction table for tables1 & 3 so with it is it okey if i will not make both a primary key?

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by alexyeth
    so with it is it okey if i will not make both a primary key?
    is it okay? well, yes, provided that you really do wish to allow the possibility of the same pubid being related to the same pubcode more than once

    in most many-to-many relationships, this would be an error
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jun 2007
    Posts
    28
    so what's the best way i could do it?

    thanks

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    create a composite primary key on both columns
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    You have a composite primary key - this is one primary key comproised of 2+ columns.

    i.e.
    Code:
    CREATE TABLE MyTable
    (
    pubid int 
    , pub char(1) 
    , publ char(1) 
    , pubcode char(2)
    , CONSTRAINT pk_MyTable PRIMARY KEY CLUSTERED (pubid, pubcode)
    )
    Again I recommend you read the link. It is only an article not a whole book but it will help you get some of the basics figured out.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •