How to make id field auto increment in db_schema.xml in mageno 2.4?

Question

I have a custom table created through db_schema.xml (code below):

<?xml version="1.0"?>
<schema xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xsi:noNamespaceSchemaLocation="urn:magento:framework:Setup/Declaration/Schema/etc/schema.xsd">
    <table name="Magebit_Faq_Question">
        <column xsi:type="int" name="id" unsigned="true" nullable="false" padding="10" identity="true" comment="id"/>
        <column xsi:type="text" name="question" nullable="false" comment="Question"/>
        <column xsi:type="text" name="answer" nullable="false" comment="Answer"/>
        <column xsi:type="smallint" name="status" nullable="false" default="0" comment="Status"/>
        <column xsi:type="int" name="position" nullable="false" default="0" comment="Position"/>
        <column xsi:type="timestamp" name="updated_at" on_update="true" default="CURRENT_TIMESTAMP" comment="Time of event"/>
        <constraint xsi:type="primary" referenceId="PRIMARY">
            <column name="id"/>
        </constraint>
    </table>
</schema>

It is clear here that the primary key is id so, technically, the id field should be auto_increment. But, When I run the query below

INSERT INTO Magebit_Faq_Question(question, answer, status, position) VALUE ('what?', 'just what', 1, 0);

I get an error saying

[2022-08-09 14:32:06] [HY000][1364] Field 'id' doesn't have a default value

How can I solve this issue?

Answer 1

Like you say, the id is clearly a primary key but you have not checked if it is the case as of yet:

• I suggest first to perform the following query: describe Magebit_Faq_Question.

See below a screenshot of a table I made with a similar schema to yours

If you don't see the auto-increment in the result for this query, then we have an answer to your question.