Coalesce not working in insert statement for a null table

Question

I am trying to use the coalesce function in SQL to avoid getting an error when inserting a row with an auto-incrementing uid into a table that is null. However, the following code is still giving me:

"cannot insert the value null into column 'TABLE_ONE_UID'".

cmdEx.ExecuteNonQuery(
    "INSERT INTO TABLE_ONE 
            (TABLE_ONE_UID, USER_UID, SHT_DATE, 
            C_S_UID, CST_DATE, 
            CET_DATE, S_M, PGS) 
    VALUES ((SELECT MAX(COALESCE(TABLE_ONE_UID, 0)) + 1 
                FROM TABLE_ONE), 
            127, '2009-06-15T13:45:30', 
            0, '2009-06-15T13:45:30','2010-06-15T13:45:30', 
            'TEST DELETE THIS ROW', 0 )");

Answer 1

The correct way to solve this is with an auto_increment column:

create table PMS_CALC_SCHEDULE (
    PMS_CALC_SCHEDULE_UID int auto_increment primary key,
    . . .
);

If, for some reason, you want to do the calculation yourself, subject your code to race conditions, and have slower inserts, then you need to do the coalesce in the right place:

INSERT INTO PMS_CALC_SCHEDULE (PMS_CALC_SCHEDULE_UID, . . .) 
    SELECT COALESCE(MAX(PMS_CALC_SCHEDULE_UID), 0) + 1, 
           . . .
    FROM PMS_CALC_SCHEDULE ;