SQL help - concatenating primary index

Discussion in 'Software' started by Publius, Jun 29, 2007.

  1. Publius

    Publius Sergeant

    How do I create a primary key that is the concatenation of two columns in a table that already exists without deleting either column? For example:

    Code:
    +-------------------+---------------+------+-----+---------+-------+
    | Field             | Type          | Null | Key | Default | Extra |
    +-------------------+---------------+------+-----+---------+-------+
    | col1              | varchar(255)  | NO   |     |         |       |
    | col2              | varchar(255)  | NO   |     |         |       |
    | col3              | float         | YES  |     | NULL    |       |
    | col4              | int(11)       | YES  | PRI | NULL    |       |
    In this example, col4 is my primary key, but I have come across a situation where I need multiple col1 values associated with the same col4 value. The easy solution is to concatenate col4 and col1 as the primary key, but I can't rebuild the table from scratch because it is populated with a lot of entries.

    Thanks,
     
  2. Publius

    Publius Sergeant

    nvm. Stupid question.

    If anyone was curious, it was pretty simple:

    Code:
    ALTER TABLE table1 DROP PRIMARY KEY;
    ALTER TABLE table1 ADD PRIMARY KEY (col4, col1);
     

MajorGeeks.Com Menu

Downloads All In One Tweaks \ Android \ Anti-Malware \ Anti-Virus \ Appearance \ Backup \ Browsers \ CD\DVD\Blu-Ray \ Covert Ops \ Drive Utilities \ Drivers \ Graphics \ Internet Tools \ Multimedia \ Networking \ Office Tools \ PC Games \ System Tools \ Mac/Apple/Ipad Downloads

Other News: Top Downloads \ News (Tech) \ Off Base (Other Websites News) \ Way Off Base (Offbeat Stories and Pics)

Social: Facebook \ YouTube \ Twitter \ Tumblr \ Pintrest \ RSS Feeds