Ideas for an Auto Increment Field

Hi Phil,

MySQL iteslf doesn't allow generated columns to reference an auto incremented field (or use functions that are nondeterministic... aka have different values each time you call them like NOW()). You can see these rules at https://dev.mysql.com/doc/refman/5.7/en/create-table-generated-columns.html 

Other than that, you can use a generated column for building other values based off other non-auto incrementing fields. For example this generated column would get you most of the way there, just not with the 'num' field. Below is an example of building something similar to what you want based on the createdDate year and a column called referral_code that you might have created to have a unique referral code.

CONCAT(YEAR(createdDate), '-', referral_code)

Let me know if you have further questions. :)

Tim,

You replied before I was able to update my post.  I have been able to achieve what I wanted adding a trigger with phpMyAdmin.  The trigger is below.  I still need to test some things in the production enviroment to make sure I don't get any duplicates, but this should work.

BEGIN 

-- Increment the num value and store it in generatedNum 

SET NEW.generatedNum = (SELECT IFNULL(MAX(num), 0) + 1 FROM cmsb_oca_log); 

-- Set the lcwc_num field using the incremented generatedNum 

SET NEW.lcwc_num = CONCAT( YEAR(NEW.createdDate), '-', LPAD(NEW.generatedNum, 4, '0') ); 

END

I'm open to any ideas how to make things better so all suggestions from anyone is welcome.

Phil

Hey Phil,

Glad you got something to work with! I wasn't sure if you were comfortable with triggers or not. But from what I understand a trigger should work nicely and get around the limit of an auto-generated column.