Error when executing an INSERT in a view

Asked

Viewed 80 times

0

I have this chart

+---------------+---------------+------+-----+---------+-------+
| Field         | Type          | Null | Key | Default | Extra |
+---------------+---------------+------+-----+---------+-------+
| CNPJ          | char(14)      | NO   | PRI | NULL    |       |
| fkusuario     | int(11)       | NO   | MUL | NULL    |       |
| DataCadastro  | date          | YES  |     | NULL    |       |
| Razao         | varchar(150)  | YES  |     | NULL    |       |
| Operadora     | varchar(100)  | YES  |     | NULL    |       |
| Linhas        | int(11)       | YES  |     | NULL    |       |
| Classificacao | varchar(3)    | YES  |     | NULL    |       |
| Vigencia      | date          | YES  |     | NULL    |       |
| mesesContrato | int(11)       | NO   |     | NULL    |       |
| Fidelidade    | varchar(50)   | NO   |     | NULL    |       |
| ValorGasto    | decimal(10,2) | YES  |     | NULL    |       |
| FixoEmpresa   | varchar(17)   | YES  |     | NULL    |       |
| Gestor        | varchar(150)  | YES  |     | NULL    |       |
| Celular       | varchar(17)   | YES  |     | NULL    |       |
| FixoGestor    | varchar(17)   | YES  |     | NULL    |       |
| Email         | varchar(150)  | YES  |     | NULL    |       |
| Obs           | varchar(255)  | YES  |     | NULL    |       |
+---------------+---------------+------+-----+---------+-------+

From it I created an identical view but without the field fkusuario and I’m trying to make an insert in this view

insert into checagens_sem_fk values('8425787107104','2019-04-16', 'REDECARD S A', 'VIVO', '23', 'A', '2018-05-15', '12', 'Não Fidelizado B','850', '994901415', 'BRUNO', '15997677764', '1532594563', '[email protected]', 'teste');

But she makes a mistake:

Error Code: 1423. Field of view 'global.checagens_sem_fk' underlying table doesn’t have a default value

What could it be? can I do an Insert in a view? or is it on account of removing the foreign key.

EDIT with the creation of the view

+---------------+---------------+------+-----+---------+-------+
| Field         | Type          | Null | Key | Default | Extra |
+---------------+---------------+------+-----+---------+-------+
| CNPJ          | char(14)      | NO   |     | NULL    |       |
| DataCadastro  | date          | YES  |     | NULL    |       |
| Razao         | varchar(150)  | YES  |     | NULL    |       |
| Operadora     | varchar(100)  | YES  |     | NULL    |       |
| Linhas        | int(11)       | YES  |     | NULL    |       |
| Classificacao | varchar(3)    | YES  |     | NULL    |       |
| Vigencia      | date          | YES  |     | NULL    |       |
| mesesContrato | int(11)       | NO   |     | NULL    |       |
| Fidelidade    | varchar(50)   | NO   |     | NULL    |       |
| ValorGasto    | decimal(10,2) | YES  |     | NULL    |       |
| FixoEmpresa   | varchar(17)   | YES  |     | NULL    |       |
| Gestor        | varchar(150)  | YES  |     | NULL    |       |
| Celular       | varchar(17)   | YES  |     | NULL    |       |
| FixoGestor    | varchar(17)   | YES  |     | NULL    |       |
| Email         | varchar(150)  | YES  |     | NULL    |       |
| Obs           | varchar(255)  | YES  |     | NULL    |       |
+---------------+---------------+------+-----+---------+-------+
mysql database
  • 1

    You can put the view creation script in the question?

  • This error usually occurs when using a insert without column names and one of the uninformed columns does not have a default value defined.

  • @vnbrs I edited the question

  • 1

    The view is just a projection of the table data it represents. When you enter an editable view actually the insertion is taking place in the table that gives rise to the view data and in your case there is a foreign key fkusuario INT NOT NULL that requires a value and that key not referenced by the view which will result in an error every time a INSERT is done in the vision.

  • 1

    @Augustovasques understood, I will have to look for another alternative so, thank you ^^

1 answer

2

The databases usually do not allow to make updates in views, in some one can even configure it in the creation of the view but goes a lot of database, as views do not pass a selection of several tables it is almost impossible to cascade the data correctly in all, as in which they have sequences. If it helps I leave this link that explains the case well:

https://www.w3resource.com/sql/update-views/sql-update-views.php

Another good option is to read the database documentation in relation to the views.

Hug.

  • Interesting, but in my case it is an INSERT as @Augustovasques quoted is on account of the foreign key I thought q a view became a virtual table but it is simply a projection .

Browser other questions tagged mysql database

You are not signed in. Login or sign up in order to post.