I have a table with an auto-increment ID field as shown below.
+------------+-------------------------------------+ | company_id | name | +------------+-------------------------------------+ | 1 | International Client | | 2 | Oracle | | 3 | test | | 4 | testabc | | 5 | testdef | | 6 | abcd | +------------+-------------------------------------+
I want to update the ID column to be a GUID using the
uuid()function.
Additionally, how do I update the foreign key references to the correct GUID?
Auto-increment allows a unique number to be generated automatically when a new record is inserted into a table. Often this is the primary key field that we would like to be created automatically every time a new record is inserted.
In MySQL, the syntax to reset the AUTO_INCREMENT column using the ALTER TABLE statement is: ALTER TABLE table_name AUTO_INCREMENT = value; table_name. The name of the table whose AUTO_INCREMENT column you wish to reset.
In MySQL, the syntax to change the starting value for an AUTO_INCREMENT column using the ALTER TABLE statement is: ALTER TABLE table_name AUTO_INCREMENT = start_value; table_name. The name of the table whose AUTO_INCREMENT value you wish to change.
ALTER TABLE `table` AUTO_INCREMENT = number; Replacing 'number' with the result of the previous command plus one and replacing table with the table name. If you deleted all the rows in the table, then you could run the alter table command and reset it to 0.
Use triggers.
CREATE TABLE `tbl_test` (
`GUID` char(40) NOT NULL,
`Name` varchar(50) NOT NULL,
PRIMARY KEY (`GUID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
table and pk, now trigger..
DELIMITER //
CREATE TRIGGER `t_GUID` BEFORE INSERT ON `tbl_test`
FOR EACH ROW begin
SET new.GUID = uuid();
end//
DELIMITER ;
Now try,
insert into tbl_test(Name) value('trigger happy...');
regards, /t
you can't use it with autoincrement
guid is char not intger
you need to insert it your self
also you will need to change the id to char(40)
insert into table_name (id,name) values (uuid(),'jon');
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With