I want a column to default to a GUID, so if I am doing an insert and I don't explicitly set the value, I want it to default to a new GUID value.
how can I do this?
default Guid is {00000000-0000-0000-0000-000000000000} . It's basically binary zeroes.
The DEFAULT() function returns the default value for table column. DEFAULT value of a column is a value used in the case, there is no value specified by user. In order, to use this function there should be a DEFAULT value assign to the column.
A column's default value is part of its definition, but can be modified separately from other aspects of the definition. To change a default value, use ALTER col_name SET DEFAULT : ALTER TABLE mytbl ALTER j SET DEFAULT 1000; Default values must be constants.
Default values can be NULL, or they can be a value that matches the data type of the column (number, text, date, for example).
Previous answer is not quite right - you've got to be careful with triggers... they will actually overwrite any default value you pass in if used as in that example. Everything will work fine when the primary key is not set, but if you pass one in with the INSERT it will get wiped with a new random one by the trigger.
For it to work properly you must check whether the field already has a value before assigning a new one, as follows:
DELIMITER ;;
CREATE TRIGGER `sometrigger`
BEFORE INSERT ON `sometable`
FOR EACH ROW
BEGIN
IF ASCII(NEW.uuid) = 0 THEN
SET NEW.uuid = UNHEX(REPLACE(UUID(),'-',''));
END IF;
SET @last_uuid = NEW.uuid;
END
;;
I use ASCII()
to check the new field value, as ASCII()
will return 0 for an empty string whether the data is in textual or binary format (and an empty string is the default value for fields with no default set). I also use binary(16) to store my UUID's for most efficient storage space and query speed... if you don't want to deal with the complexities of binary fields then you can simply use UUID()
in place of UNHEX(REPLACE(UUID(),'-',''))
with a char(36) field.
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