Table is not being created with DEFAULT values

Question

I want to create a table named as review with some DEFAULT's as shown in the following code:

mysqli_query($link, "CREATE TABLE IF NOT EXISTS `review` (
            `clients_id` int(15) NOT NULL AUTO_INCREMENT,
            `client_id` varchar(150) NOT NULL DEFAULT '',
            `rating` tinyint(2) NOT NULL DEFAULT '0',
            `proj_date` date NOT NULL DEFAULT '0000-00-00',
            `proj_desc` text NOT NULL DEFAULT '',
            `photoname` text NOT NULL,
            `companyname` text NOT NULL,
            `feedback` text NOT NULL,
            `status` tinyint(1) NOT NULL DEFAULT '0',
            `emailid` varchar(100) NOT NULL DEFAULT '',
            `customratings` varchar(100) NOT NULL DEFAULT '',
            `photo_option` varchar(100) NOT NULL DEFAULT '',
            `title` varchar(100) NOT NULL DEFAULT '',
            `citation` varchar(100) NOT NULL DEFAULT '',
            `date_option` varchar(100) NOT NULL DEFAULT '',
            `rating_option` varchar(100) NOT NULL DEFAULT '',
            PRIMARY KEY (`clients_id`),
            FULLTEXT KEY `feedback` (`feedback`)
            ) ENGINE=MyISAM  AUTO_INCREMENT=1") or mysqli_error($link);

Columns of this table should contain DEFAULT values, but if write DEFAULT values table is not being created.

Now, I replaced my code by removing DEFAULT values with following :

mysqli_query($link, "CREATE TABLE IF NOT EXISTS `review` (
        `clients_id` int(15) NOT NULL AUTO_INCREMENT,
        `client_id` varchar(150) NOT NULL,
        `rating` tinyint(2) NOT NULL,
        `proj_date` date NOT NULL,
        `proj_desc` text NOT NULL,
        `photoname` text NOT NULL,
        `companyname` text NOT NULL,
        `feedback` text NOT NULL,
        `status` tinyint(1) NOT NULL,
        `emailid` varchar(100) NOT NULL,
        `customratings` varchar(100) NOT NULL,
        `photo_option` varchar(100) NOT NULL,
        `title` varchar(100) NOT NULL,
        `citation` varchar(100) NOT NULL,
        `date_option` varchar(100) NOT NULL,
        `rating_option` varchar(100) NOT NULL,
        PRIMARY KEY (`clients_id`),
        FULLTEXT KEY `feedback` (`feedback`)
        ) ENGINE=MyISAM  AUTO_INCREMENT=1") or mysqli_error($link);

Now table is being created perfectly, and if continue with this (without defaults) table I am getting 1364 MySQL error while inserting data

I temporary fixed this with mysql error 1364 Field doesn't have a default values by removing STRICT_TRANS_TABLES of my sqlmode setting in phpmyadmin

I also tried creating the table first and then ADD default to it by using ALTER TABLE but this could be done only once and not correct for lengthy database..

But I don't want to change my phpmyadmin setting and create table with DEFAULT column values. How can I do that and where I am doing wrong?

Thanks

Nawaz Ghori

One tip is to try run the SQL in something like phpmyadmin, this will show you any errors and allow you to try out different things. Then include it in your PHP code one you have it working. — Nigel Ren, Nov 29 '17 at 08:14

score 1 · Answer 1 · answered Nov 29 '17 at 07:48

It's not saving into database definitely because the field column_name (and maybe some others) is checked as "NOT NULL". It means that the value of that field must be something other than NULL (NULL - no data at all)

It means you have 2 options:

Mark your field as NULL (first check if your field is required to have some value or not).

ALTER TABLE your_table CHANGE COLUMN your_field your_field VARCHAR(250) NULL; Add a default value to the field so if no data is provided on an insert, it will put something you defined. For example:

ALTER TABLE your_table CHANGE COLUMN your_field your_field VARCHAR(250) NOT NULL DEFAULT 'some_default_value';

And of course, match your field type to the field you are going to change.

score 1 · Accepted Answer · answered Nov 29 '17 at 08:03

There are a couple of problems with the SQL. The first is that your trying to default to date to an invalid date ('0000-00-00' isn't a valid date). Also with text fields, why set it to default to '', you cannot set a default value for a few field types - text is one of them (https://dev.mysql.com/doc/refman/5.7/en/data-type-defaults.html).

SO...

CREATE TABLE IF NOT EXISTS `review` (
            `clients_id` int(15) NOT NULL AUTO_INCREMENT,
            `client_id` varchar(150) NOT NULL DEFAULT '',
            `rating` tinyint(2) NOT NULL DEFAULT '0',
            `proj_date` date NOT NULL DEFAULT '1900-01-01',
            `proj_desc` text NOT NULL,
            `photoname` text NOT NULL,
            `companyname` text NOT NULL,
            `feedback` text NOT NULL,
            `status` tinyint(1) NOT NULL DEFAULT '0',
            `emailid` varchar(100) NOT NULL DEFAULT '',
            `customratings` varchar(100) NOT NULL DEFAULT '',
            `photo_option` varchar(100) NOT NULL DEFAULT '',
            `title` varchar(100) NOT NULL DEFAULT '',
            `citation` varchar(100) NOT NULL DEFAULT '',
            `date_option` varchar(100) NOT NULL DEFAULT '',
            `rating_option` varchar(100) NOT NULL DEFAULT '',
            PRIMARY KEY (`clients_id`),
            FULLTEXT KEY `feedback` (`feedback`)
            ) ENGINE=MyISAM  AUTO_INCREMENT=1

does work.

BUT - you may want to set some particular date for proj_date.

Thanks for noticing!! ..It was silly me doing simple mistakes — Nawaz Ghori, Nov 29 '17 at 08:14

score 0 · Answer 3 · answered Nov 29 '17 at 08:17

one of the problem could be that you have with the first block is :

`rating` tinyint(2) NOT NULL DEFAULT '0',
....
`status` tinyint(1) NOT NULL DEFAULT '0',

instead, you need to have 0 without quote:

............................ DEFAULT 0,

also, default values are not set for some of column, set defaults for them too.

Table is not being created with DEFAULT values

3 Answers3