SitePoint Sponsor

User Tag List

Results 1 to 11 of 11
  1. #1
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    CHANGING primary key

    Hi,

    I want to change my primary key from being one col to being three. I can't seem to find a way of 'change primary key'. all I find is 'drop' and 'create'.

    With use of the MySQL manual I have created a script to replace the current PK with a new one.
    I don't want to break the table so, would someone please advise me if this is correct.

    Code:
    ALTER TABLE `business_type`
    DROP PRIMARY KEY
    ADD INDEX business_type_ix PRIMARY KEY ( business_type, business_sub_type, business_catgeory ) PRIMARY KEY
    bazz

  2. #2
    SitePoint Addict wibble wobble's Avatar
    Join Date
    Dec 2008
    Posts
    242
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    A RDBMS must always have a primary key, so I dont know if that would work. You might have to create a compound key with all 4 fields, then drop the original PK out of that.

    Edit: Typo - RDBMS as RMDB
    Find freelance jobs from all the major sites in one place:
    on twitter / on the web / twitter rss feed

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,322
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by IBazz View Post
    I don't want to break the table so, would someone please advise me if this is correct.
    one word: test database

    break stuff to your heart's content, then apply it to your real database

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,322
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by wibble wobble View Post
    A RMDB must always have a primary key,
    whatever an RDMB might be, that particular rule does not apply to any database system like MySQL, SQL Server, eck settera
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Addict wibble wobble's Avatar
    Join Date
    Dec 2008
    Posts
    242
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    whatever an RDMB might be, that particular rule does not apply to any database system like MySQL, SQL Server, eck settera
    Thanks for ripping into my typo

    I never knew that second part - interesting.
    Find freelance jobs from all the major sites in one place:
    on twitter / on the web / twitter rss feed

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,322
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    yeah, sorry, i know you meant RDBMS (relational database management system), i was just having you on

    it's certainly easy enough to test -- you do have a test database for this stuff just like bazz has, right?
    Code:
    CREATE TABLE nopk ( foo INTEGER ) ;
    INSERT INTO nopk VALUES ( 9 ) , ( 37 ) , ( 937 ) , ( 9 );
    SELECT * FROM nopk;
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Addict wibble wobble's Avatar
    Join Date
    Dec 2008
    Posts
    242
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I have a scribbes DB (and a scribbles project in my IDE) if that counts!

    Forget the rest of this post. I'm blind.

    Did a few queries on PK-less tables. The only bug I got was getting 2 rows when there was only 1 match:

    Code sql:
    CREATE TABLE nopk ( foo INTEGER ) ;
    INSERT INTO nopk VALUES ( 9 ) , ( 37 ) , ( 937 ) , ( 9 );
     
    CREATE TABLE nopk2 ( foo2 INTEGER ) ;
    INSERT INTO nopk2 VALUES ( 91 ) , ( 337 ) , ( 9437 ) , ( 9 );
     
    SELECT * FROM `nopk2` JOIN `nopk` ON foo = foo2

    That returned

    Code:
    foo2 	foo
    9 	9
    9 	9
    Find freelance jobs from all the major sites in one place:
    on twitter / on the web / twitter rss feed

  8. #8
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    one word: test database
    That's two words where I come from.

    couldn't resist

    anyway, I still get an error.

    Code:
    ALTER TABLE `business_type`
    , DROP PRIMARY KEY
    , ADD INDEX business_type_ix
    , PRIMARY KEY (business_type,business_sub_type,business_category);
    #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' PRIMARY KEY (business_type,business_sub_type,business_category)' at line 4
    I tried to do it with phpMyAdmin and was only able to create a unique index. when I then tried to remove the PK, it told me that I couldn't because there 'could only be one auto_increment column and it had to be the PK' (or words to that effect. Stands to reason I suppose.

    And maybe I only need a unique index anyway? I am just trying to prevent duplicate entries and this has altered my table to createa unique index .

    Code:
     ALTER TABLE `tbl_business_type` ADD UNIQUE `business_type_ix` ( `business_type` , `business_sub_type` , `business_category` )
    not much like the docs said it should be done.
    bazz

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,322
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    the error message almost always tells you exactly where the error occurred

    you forgot the ADD keyword in front of PRIMARY KEY

    before you decide between primary and unique keys, you need to settle on whether you need a surrogate key (auto_increment) or not
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,322
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    as for two columns in the same row called "type" and "subtype", you may want to rethink this

    you're embedding a relationship (between type and subtype) into a third table, and you're leaving open the possibility that row can reference a type and subtype that aren't actually related to each other

    better to link just to the (sub)type, and have the (sub)type link to its (super)type

    see Categories and Subcategories

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks Rudy.

    I won't need the auto_increment in the end. But for now, and until I get my head around the alternative, I do need it. By alternative I mean where the three fields are normalised throughout the table and have PK/FK relationships within the table. (I'm sure there's a name for dat).

    bazz


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
  •