1

i'm attempting to create a table with an incremental ID. This isn't the simple id with AUTO_INCREMENT. In fact, the exact ID that i'm trying to work with is:

test.script.1/person.n (where n is an incrementing number)

Specifically, I have a table structured as:

ID | fname | lname | gender | age |

Each person that I add in, I wish to have and incremental ID. So for person 1, it would be test.script.1/person.1 and then test.script.1/person.2 for the second entry and so on and so forth.

Is it possible to do this? I have searched for a while now in Google, but I do not think I am searching for the right thing. My apologies if there isn't that much information, and it is a simple question, I just don't know what to look for. Please advise.

Thanks guys!

Michael
  • 107
  • 1
  • 2
  • 9

1 Answers1

1

You can use an autoincrementing field together with a trigger to populate the actual field you wish to use for your reference.

Something like this:

CREATE TRIGGER set_my_uuid AFTER INSERT ON myTable
FOR EACH ROW
BEGIN
SET NEW.uuid_ref_col = concat('test.script.1/person.', NEW.my_auto_increment_col)
END
;

where my_auto_increment_col is a standard autoincrement column in the table.

davek
  • 22,499
  • 9
  • 75
  • 95
  • Would uuid_ref_col remain the same? – Michael Jul 01 '11 at 13:35
  • 1
    @Michael: In davek's example, you would not supply `my_auto_increment_col` or `uuid_ref_col` in your `INSERT` statement. They would both be generated, but it would produce what you want. You would just have an `auto_increment` column **and** the id column you want. – Joel B Fant Jul 01 '11 at 13:44
  • Ah, that makes much more sense, thank you for the clarification. – Michael Jul 01 '11 at 13:47
  • Actually, upon further research (now that I know what to look for), SET NEW.col_name = value does not work with the AFTER function. Only before. – Michael Jul 01 '11 at 14:15