INSERT unique value to MYSQL

Question

I am trying to insert a unique value into my table, however I need to know the ID before I create it. I know an AUTO_INCREMENT would have solved this problem, but this field is not AUTO_INCREMENTed.

This is my code...

INSERT INTO networks 
    (NETWORK_ID, ADMIN_USER_ID, NETWORK_NAME, ADDRESS)
VALUES 
    ((SELECT MAX(NETWORK_ID)+1 FROM networks) , 3, 'Arcafe', 'habarzel 2 TA')

When I run it, I get a warning that I can't use the table in the FROM, I guess because it is pointing to itself. How can I achieve what I need? Can I change a field into an AUTO_INCREMENT field?

This link will help you http://stackoverflow.com/questions/1467581/how-to-generate-unique-id-in-mysql — Roshan Wijesena, Aug 19 '11 at 10:57

a'r · Accepted Answer · 2011-08-19T12:12:48.253

3

Usually you set the field to be an auto increment field when it is defined. To do so afterwards, you can use:

ALTER TABLE networks MODIFY NETWORK_ID int(10) unsigned NOT NULL auto_increment;

To then insert an ew record and for it to automatically get an assigned ID, merely omit the field from the insert, eg.

INSERT INTO networks 
    (ADMIN_USER_ID, NETWORK_NAME, ADDRESS)
VALUES 
    (3, 'Arcafe', 'habarzel 2 TA')

edited Aug 19 '11 at 12:12

answered Aug 19 '11 at 10:53

a'r

35,921
7
66
67

Thanks for the ALTER advice for the AUTO_INCREMENT. I am looking for a solution for the INSERTion of a unique ID. – Ted Aug 19 '11 at 11:19
I went ahead and used the ALTER, proves to be the best idea. Thanks ! – Ted Aug 19 '11 at 12:29

score 1 · Answer 2 · answered Aug 19 '11 at 10:51

1

You can use separate table for generation unique ids and use this ids

answered Aug 19 '11 at 10:51

varela

1,281
1
10
16

MySQL has auto_increment for this purpose, a separate table is unnecessary and error prone. – a'r Aug 19 '11 at 10:54
That's an interesting idea! I thought I could use a seperate table that IS auto_incremented, and use that number. It would require an extra SELECT though. I am trying to stick to pure php/mysql as it is right now though. – Ted Aug 19 '11 at 11:21

score 1 · Answer 3 · answered Aug 19 '11 at 10:52

1

ALTER TABLE networks CHANGE NETWORK_ID NETWORK_ID int auto_increment

Alter Table Manual

answered Aug 19 '11 at 10:52

RiaD

46,822
11
79
123

Thanks for the ALTER advice for the AUTO_INCREMENT. I am looking for a solution for the INSERTion of a unique ID. – Ted Aug 19 '11 at 11:19

INSERT unique value to MYSQL

3 Answers3