SitePoint Sponsor

User Tag List

Results 1 to 10 of 10

Thread: Multi field PKs

Hybrid View

  1. #1
    Prolific Blogger silver trophy Technosailor's Avatar
    Join Date
    Jun 2001
    Location
    Before These Crowded Streets
    Posts
    9,446
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Multi field PKs

    Hey everyone,

    Although I'm using MySQL, the idea is that this can go to SQL or another DB server if necessary... so as generic as possible.

    I'm writing a stat package for tracking video game stats from an RSS feed. I made a mistake and wrote this as a personal venture and only intended to use my own stats. Naturally, I used the 8-digit gameid as my primary key because, it would only appear once in my stats.

    The problem crept up when I decided to make it a multi-user system. If two users played in the same game, the gameid would be identical and thus a duplicate in the db (and thus unable to be a PK).

    My thought was to use a multi-field PK consisting of the username and gameid.

    Problem is I have never used a multi-field PK and I'm concerned how to do this without losing my db. (Of course, I did back it up in case of such an eventuality).

    But I'm looking for the best way to alter this table without losing the relevance of the data already used (follow me?).

    The table schema is as follows:
    Code:
     CREATE TABLE halo2 (
       gameid int(13) NOT NULL default '0',
       gamertag varchar(20) NOT NULL default '',
       gamestyle varchar(25) NOT NULL default '',
       gamemode varchar(25) NOT NULL default '',
       gamemap varchar(15) NOT NULL default '',
       link varchar(100) NOT NULL default '',
       playdate varchar(30) NOT NULL default '',
       score int(5) NOT NULL default '0',
       kills int(5) NOT NULL default '0',
       deaths int(5) NOT NULL default '0',
       assists int(5) NOT NULL default '0',
       PRIMARY KEY  (gameid)
     ) TYPE=MyISAM;
    Aaron Brazell
    Technosailor



  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,021
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Code:
    alter table halo2 
    drop primary key
    ;
    alter table halo2 
    add column username varchar(12) not null default 'shemp'
    ;
    alter table halo2 
    add primary key (username ,gameid)
    ;
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    Prolific Blogger silver trophy Technosailor's Avatar
    Join Date
    Jun 2001
    Location
    Before These Crowded Streets
    Posts
    9,446
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    username is actually gamertag. I didn't specify that.

    So just:
    Code:
     alter table halo2 drop primary key;
     alter table halo2 add primary key(gamertag,gameid);
    ?
    Aaron Brazell
    Technosailor



  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,021
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    yeah, run that, i'm pretty confident it will work
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    Prolific Blogger silver trophy Technosailor's Avatar
    Join Date
    Jun 2001
    Location
    Before These Crowded Streets
    Posts
    9,446
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    sweet, I think it works. No errors. Now hopefully I won't run into problems inserting identical gameids...
    Aaron Brazell
    Technosailor



  6. #6
    Prolific Blogger silver trophy Technosailor's Avatar
    Join Date
    Jun 2001
    Location
    Before These Crowded Streets
    Posts
    9,446
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That would work on other DB systems too?
    Aaron Brazell
    Technosailor



  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,021
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    the concepts of dropping a primary key constraint, and adding a compound primary key constraint? yes

    the exact same ALTER syntax? maybe not
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    Prolific Blogger silver trophy Technosailor's Avatar
    Join Date
    Jun 2001
    Location
    Before These Crowded Streets
    Posts
    9,446
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    IOW, what would be the CREATE TABLE syntax with multi-key for say, SQL 2000?
    Aaron Brazell
    Technosailor



  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,021
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    it's not really a surprise...
    Code:
    create table sketch
    ( foo integer not null 
    , bar integer not null 
    , primary key ( foo, bar )
    )
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Guru
    Join Date
    Jan 2004
    Location
    Uppsala, sverige
    Posts
    695
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)
    Code:
    alter table t
    drop primary key
    will not work on most DBMS. The standard method is to use the constraint name

    Code:
    alter table t
    drop constraint <constraintName>
    The constraint name can be assigned when defining the primary key

    Code:
    create table t(c1 int, constraint PrimarykeyForT primary key(c1))
    You can get the constraint name by quering the information_schema views. This is not supported by all DBMS, so you have to check the documemtation how it is done for those who does not comply with the standard.


Bookmarks

Posting Permissions

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