How to create table in MariaDB?

Question

When I try to create simple table via HeidiSQL I'm getting an error like this

CREATE TABLE `prg_config` (
    `id` INT NULL AUTO_INCREMENT,
    `name` VARCHAR(50) NULL DEFAULT '',
    `value` VARCHAR NULL DEFAULT ''
) COLLATE='utf8_bin';

Answer 1

Please Check Following query :

CREATE TABLE prg_config (
     `id` INT NOT NULL AUTO_INCREMENT,
     `name` VARCHAR(50) NULL DEFAULT '',
     `value` VARCHAR(50) NULL DEFAULT '',
     PRIMARY KEY (id)
)COLLATE='utf8_bin';

Answer 2

CREATE TABLE `prg_config` (
        `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
        `name` VARCHAR(50) NULL DEFAULT '',
        `value` VARCHAR(100) NULL DEFAULT ''
    ) COLLATE='utf8_bin';

1. Add PRIMARY KEY/UNIQUE/KEY to AUTO_INCREMENT column

2. Specify length for VARCHAR.

AUTO_INCREMENT :

Each table can have only one AUTO_INCREMENT column. It must defined as a key (not necessarily the PRIMARY KEY or UNIQUE key). If the key consists of multiple columns, the AUTO_INCREMENT column must be the first one, unless the storage engine is Aria or MyISAM.

SqlFiddleDemo

Answer 3

If you created that table with HeidiSQL's table designer, I guess it looked like this:

HeidiSQL does not complain when you make the length/set empty for a VARCHAR column. MySQL + MariaDB both require a length for VARCHAR columns, so I can probably fix that by not letting the user to make the VARCHAR length empty.