1

This is my database (just for example):

+----------------+---------------+------+-----+---------+---------------+
| Field          | Type          | Null | Key | Default | Extra         |
+----------------+---------------+------+-----+---------+---------------+
| id             | integer       | NO   |PK   | NONE    |AUTO_INCREMENT |
| name           | string        | NO   |     | NONE    |               |
| email          | string        | YES  |     | NULL    |               |
| phoneNumber    | string        | YES  |     | NULL    |               |
| password       | string        | NO   |     | NONE    |               |
| maxDistance    | integer       | NO   |     | NONE    |               |
| showMeOnApp    | integer       | NO   |     | NONE    |               |
| created_at     | datetime      | NO   |     | NONE    |               |
| updated_at     | datetime      | NO   |     | NONE    |               |
+----------------+---------------+------+-----+---------+---------------+

So the problem here is that when I make an insert on phpMyAdmin without adding any value on password or on created_at for example, fields that should have a value, it still insert the row even if I don't set a value. Why this? I think this should give me an error saying something like: Password can't be null, maxDistance can't be null... you know? Something like that, what I'm getting now is this:

1 row inserted
Warning: #1366 Incorrect integer value: '' for column `databasename`.`users`.`maxDistance` at row 1
Warning: #1366 Incorrect integer value: '' for column `databasename`.`users`.`showMeOnApp` at row 1
Warning: # 1265 Truncated data for column 'created_at' in row 1
Warning: # 1265 Truncated data for column 'updated_at' in row 1

But insert anyway.

So this is my question, why it still insert the row if I don't satisfy all the conditions?

kmoser
  • 8,780
  • 3
  • 24
  • 40
Diego Bittencourt
  • 595
  • 10
  • 28
  • remove `default null` from the table... – Antony Jack Apr 10 '20 at 01:28
  • actually just `email` and `phoneNumber` is DEFAULT `null` – Diego Bittencourt Apr 10 '20 at 01:35
  • make others `DEFAULT NOT NULL`... – Antony Jack Apr 10 '20 at 01:52
  • @Antony Jack There isn't this option `DEFAULT NOT NULL` – Diego Bittencourt Apr 10 '20 at 02:41
  • Where is the `INSERT` query? – FanoFN Apr 10 '20 at 03:55
  • @tcadidot0 I did on PhpAdmin the Insert, wasn't me who created the query. – Diego Bittencourt Apr 10 '20 at 03:58
  • Can you see the inserted data? Are the columns inserted with NULL values or empty values? I assume that its empty because of this error example `Warning: #1366 Incorrect integer value: '' for column `databasename`.`users`.`maxDistance`. It seems like the query is inserting empty (' ') value on an integer datatype column.. which I think will instead be inserted with 0 (zero) instead of empty value – FanoFN Apr 10 '20 at 04:04
  • Even if I do: `insert into users(name) values('diego')` still insert the row, and yes, it's replaced by zero the integer fields, also dateTime fields is replaced by `0000-00-00 00:00:00` – Diego Bittencourt Apr 10 '20 at 04:08
  • Yes, that's true @DiegoBittencourt. It will insert nonetheless because it's only being set as `NOT NULL`. It won't insert if you specify to insert `NULL` value into a `NOT NULL` column; that's where you'll receive `Error: ER_BAD_NULL_ERROR: Column 'columnName' cannot be null`. Here is [a simple example](https://www.db-fiddle.com/f/g5cGSgDQdjXRSaBC76vdLW/4) – FanoFN Apr 10 '20 at 05:31

1 Answers1

2

You don't seem to understand what the Null attribute means for a column's schema. When it is set to TRUE, it means you are allowed to insert a NULL value for that column; if you fail to specify a value for that column, it will default to the NULL value.

When the Null attribute in the schema is FALSE, it means you may not set that column to NULL, and attempting to do so will possibly generate a warning, and possibly set the value of that column to "" (empty string) depending on which version of MySQL you're using and how it's configured. In this case, if you fail to specify a value for that column, it will default to "" (empty string).

kmoser
  • 8,780
  • 3
  • 24
  • 40
  • Yes I understand that, but on PostgreSQL for example it will not let me insert hte row if I don't set a value on the `not null` fields. Do you know if there's a option on MySQL to make it not allow me to insert if I don't satisfy all conditions? – Diego Bittencourt Apr 11 '20 at 18:59
  • But you have satisfied all conditions: you have provided the database with the minimum information necessary to insert a row. If you are asking how to configure MySQL to require a field, you can [see that question answered here](https://stackoverflow.com/questions/27926889/how-do-i-require-a-mysql-field). – kmoser Apr 12 '20 at 02:41