Returning null value when creating table in Wordpress

Question

I am creating a Plugin and need that when activating it create the table referent in DB, but dbDelta is returning that the table was created, but when checking using $wpdb->get_var returns a null value. That is, the table is not actually being created in DB, I already checked through Phpmyadmin.

What should be the problem in the following function?

function main(){
    global $wpdb;

    $prefix = $wpdb->prefix;
    $leads = $prefix . 'leads';

    if($wpdb->get_var("SHOW TABLES LIKE $leads") != $leads):
        $query = 
            "CREATE TABLE $leads (
                id int NOT NULL AUTO_INCREMENT, 
                nome varchar(64) NULL, 
                email varchar(64) NULL, 
                telefone varchar(15) NULL, 
                celular varchar(15) NULL, 
                mensagem tinytext NULL
            );";

        // REFERENCE TO upgrade.php FILE
        require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
        $var = dbDelta($query);
        var_dump($var); // RETORNA = array(1) { ["ic_leads"]=> string(22) "Created table ic_leads" }
    endif;

    var_dump($wpdb->get_var("SHOW TABLES LIKE $leads")); // RETORNA = NULL
}

Answer 1

I tested the structure of your table directly in the database and returned the following error:

Error Code: 1075. Incorrect table Definition; there can be only one auto column and it must be defined as a key

That is, it is necessary to define the primary key of this table and it must be the field auto increment, in the case id.

leave the structure of your table like this:

CREATE TABLE $leads (
                id int NOT NULL AUTO_INCREMENT, 
                nome varchar(64) NULL, 
                email varchar(64) NULL, 
                telefone varchar(15) NULL, 
                celular varchar(15) NULL, 
                mensagem tinytext NULL,
                primary key(id)
            );