Default value in sql laravel table

Question

I not sure if this is related to Laravel or not but I created the table with Laravel. I've got a table called programmers

DESC programmers;
+--------------+---------------------+------+-----+---------+----------------+
| Field        | Type                | Null | Key | Default | Extra          |
+--------------+---------------------+------+-----+---------+----------------+
| id           | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| name         | varchar(255)        | NO   |     | NULL    |                |
| age          | int(11)             | NO   |     | NULL    |                |
| created_at   | timestamp           | YES  |     | NULL    |                |
| updated_at   | timestamp           | YES  |     | NULL    |                |
| framework_id | int(10) unsigned    | NO   |     | NULL    |                |
| test         | tinyint(1)          | NO   |     | NULL    |                |
+--------------+---------------------+------+-----+---------+----------------+

as you can see there's a column called test that's not nullable and has a default value of null. When I to run the following command from the database I expected an error

INSERT INTO  programmers (name, age, framework_id) VALUES ('Melly2', 19, 2)

it actually worked fine and here's the data

SELECT * FROM programmers;
+----+--------+-----+---------------------+---------------------+--------------+------+
| id | name   | age | created_at          | updated_at          | framework_id | test |
+----+--------+-----+---------------------+---------------------+--------------+------+
|  1 | melly  |  20 | 2022-05-03 16:36:12 | 2022-05-03 16:36:12 |            1 |    0 |
|  2 | Melly2 |  19 | NULL                | NULL                |            2 |    0 |
+----+--------+-----+---------------------+---------------------+--------------+------+

the test column actually defaulted to 0 not null, and if I were to run the following command it tells me I can't have null as a value as expected

INSERT INTO  programmers (name, age, framework_id, test) VALUES ('Melly2', 19, 3, null);
ERROR 1048 (23000): Column 'test' cannot be null

question: can someone briefly explain why test column didn't default to null?

Old MySQL versions had that odd behavior, but it has been fixed for years now. — jarlh, May 03 '22 at 17:02
https://dba.stackexchange.com/questions/156159/field-both-not-null-and-default-null — Esther, May 03 '22 at 17:03
as I understand this, "default set to null" means that you didn't set a default. If you don't enter data for that column, it defaults to the default for that data type, which for `tinyint` is 0. — Esther, May 03 '22 at 17:04
No default value, and NOT NULL, means a value _has to be provided_. (However, early MySQL versions did not work that way.) — jarlh, May 03 '22 at 17:06
Refer to this https://stackoverflow.com/questions/12839927/mysql-tinyint-2-vs-tinyint1-what-is-the-difference — Asgar, May 03 '22 at 17:26

score 0 · Answer 1 · answered May 03 '22 at 17:07

In this scenario, the default value is null only if you don't provide a value. But when you provide some value, it should be compatible with the datatype you set for the column.

Here the datatype is tinyint. So, you should provide the values from true/false which infact will be converted into 1/0; else you should insert integers example:0,1,2,... etc.

Default value in sql laravel table

1 Answers1