MariaDB default function changing PK field with auto increment to null

user_17712875 0
Viewed 69

Take the example of the following query, I am inserting data to the teams table and teamId (type: INT) is the primary key with auto-increment set to true.

'INSERT INTO `teams` (`teamId`,`teamName`,`referralCommission`,`createdAt`,`updatedAt`,`companyId`) VALUES (DEFAULT,?,?,?,?,?);'

This query runs fine on MySQL, but on MariaDB the DEFAULT is being converted to null, which I know is the normal behavior when the default is not set for a column. But in my case, the teamId is auto-incremented so the default should point to the next available id. Instead, teamId is set to 0 (converts from null) for all entries and since the teamId is primary key, I am unable to add new entries to the table.

Any way I can use the default function of MySQL in mariadb? or any other solution for this problem.

P.S I know I can remove the teamId field entirely from the query and it will work, but I need the above query to work as it is.

mariadb mysql
1 Answers

i cant say what you doing. which MariaDB version you are using ?

sample

   MariaDB [bernd]> SELECT VERSION();
    +----------------------------------------+
    | VERSION()                              |
    +----------------------------------------+
    | 10.2.41-MariaDB-1:10.2.41+maria~bionic |
    +----------------------------------------+
    1 row in set (0.06 sec)
    
    MariaDB [bernd]>

    MariaDB [bernd]> TRUNCATE pk_default;
    Query OK, 0 rows affected (0.09 sec)
    
    MariaDB [bernd]> SELECT * FROM `pk_default`;
    Empty set (0.01 sec)
    
    MariaDB [bernd]> INSERT INTO `pk_default` (`id`, `sid`, `val`)
        -> VALUES
        -> (DEFAULT, 6, 45);
    Query OK, 1 row affected (0.00 sec)
    
    MariaDB [bernd]> SELECT * FROM `pk_default`;
    +----+-----+------+
    | id | sid | val  |
    +----+-----+------+
    |  1 |   6 |   45 |
    +----+-----+------+
    1 row in set (0.00 sec)
    
    MariaDB [bernd]> INSERT INTO `pk_default` (`id`, `sid`, `val`)
        -> VALUES
        -> (DEFAULT, 6, 45);
    Query OK, 1 row affected (0.01 sec)
    
    MariaDB [bernd]> SELECT * FROM `pk_default`;
    +----+-----+------+
    | id | sid | val  |
    +----+-----+------+
    |  1 |   6 |   45 |
    |  2 |   6 |   45 |
    +----+-----+------+
    2 rows in set (0.00 sec)
    
    MariaDB [bernd]> INSERT INTO `pk_default` (`id`, `sid`, `val`)
        -> VALUES
        -> (DEFAULT, 6, 45);
    Query OK, 1 row affected (0.00 sec)
    
    MariaDB [bernd]> SELECT * FROM `pk_default`;
    +----+-----+------+
    | id | sid | val  |
    +----+-----+------+
    |  1 |   6 |   45 |
    |  2 |   6 |   45 |
    |  3 |   6 |   45 |
    +----+-----+------+
    3 rows in set (0.01 sec)
    
    MariaDB [bernd]>
user_52472790
Related