Ghost Ghost -4 years ago 193
MySQL Question

Primary key UUID Trigger

I am trying to make a table where the primary key is auto generated UUID every time there's an INSERT event. But the problem is when I try to execute an INSERT query skipping the primary key column like this:

INSERT INTO `tablename` (`reference`) VALUES ('hey');


No row is inserted and no errors. Just "0 rows inserted..."
Here's my table and the trigger. Thank you.

CREATE TABLE `tablename` (
`uuid` char(36) NULL,
`reference` varchar(100) NOT NULL,
PRIMARY KEY (uuid)
);

DELIMITER ;;
CREATE TRIGGER before_insert_tablename
BEFORE INSERT ON tablename
FOR EACH ROW
BEGIN
IF new.uuid IS NULL THEN
SET new.uuid = uuid();
END IF;
END
;;

DELIMITER ;

Answer Source

You CAN NOT make a Primary key as NULL

Just change it to NOT NULL

`uuid` char(36) NOT NULL,

The primary key for a table represents the column or set of columns that you use in your most vital queries. It has an associated index, for fast query performance. Query performance benefits from the NOT NULL optimization, because it cannot include any NULL values.

Reference

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download