MySQL table updating

I've got two tables, and what I am trying to do is quite simple but quite complicated at the same time - if anyone could help me that would be great.

I've got the following tables...

Table A
ClientID
1
2
3

Table B
ClientID        ID
1               
1
3

Table B 开发者_Python百科has the ClientID's in the table and so does Table A. So they link up, what I need to do is update ID to a unique reference.

i.e. The clientID-UniqueID. For example, if I updated Table B I would want it to look like this (Sequential)

Table B
ClientID            ID
1                   1-1
1                   1-2
3                   3-1

Theres not really any reason to do this. Just us a standard auto-increment primary key on table_b.id.

Using InnoDB the schema would look like:

CREATE TABLE `table_a` (
  `id` UNSIGNED integer AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

CREATE TABLE `table_b` (
  `id` UNSIGNED integer AUTO_INCREMENT,
  `client_id` UNSIGNED integer,
  PRIMARY KEY (`id`),
  KEY (`table_a_id`),
  CONSTRAINT `table_b_table_a_id_table_a_id` 
    FOREIGN KEY (`table_a_id`)
    REFERENCES `table_a` (`id`)
    ON DELETE CASCADE
) ENGINE=InnoDB;

Youll notice some key points:

• InnoDB supports foreign keys and associated integrity enforcement actions. By using a foreign key constraint on table_b with ON DELETE CASCADE anytime an entry form table_a is deleted, all entries in table_b having the table_a_id (formerly ClientID) corresponding to the deleted entry will also be deleted automatically. Other actions can be taken instead if this is not the desired behavior.

• I have normalized your column names. For portability reasons you should NEVER use camel case column or table names. Instead use all lowercase. If you want to differentiate words in phrase use an _ as a word separator.

• You should have a consistent column and index/key naming convention. All primary keys in every table should be named in the same manner. Additionally, foreign key columns should use a naming convention that clearly identifies what foreign table and column they link to. This helps make it easier on developers when trying to write/debug/optimize queries because that dont necessarily need to inspect the actual schema to figure things out.

You may want to try something like the following (assuming ID is already a column in B):

ALTER TABLE B MODIFY COLUMN ID INT AUTOINCREMENT, ADD PRIMARY KEY (ClientID, ID)

If ID is not yet in B you can instead use

ALTER TABLE B ADD COLUMN (ID INT AUTOINCREMENT), ADD PRIMARY KEY (ClientID, ID)

I think this will make ID have a separate increment counter for each different ClientID.