Set DEFAULT value with empty string and not as NULL in Sqlite3

Asked

Viewed 226 times

0

I created a table on sqlite determining columns with value DEFAULT. The example column is "name_fantasy" and in it I informed that it would be DEFAULT '' (Empty string). When trying to insert a data into this table and do not enter value for that column, the sqlite is setting NULL. It is possible that the field has no null value?

Table creation

create table if not exists empresa(
    empresa_id integer not null default 0,          -- ID da empresa
    razao_social varchar(150) not null default '',  -- Razão social
    nome_fantasia varchar(150) not null default '', -- Nome fantasia
    primary key (empresa_id)
);

Command to enter the record

INSERT into empresa (razao_social) VALUES ("Empresa Teste");

Command to view the result after insertion

SELECT * FROM empresa;

Results from the select

inserir a descrição da imagem aqui

I performed tests using as a client the Navcat in version 10.0.3. I also tried to do the insert directly on the CLI sqlite3 and the result was the same.

One remark I noticed is that if there is some value inside the quotes when declaring the DEFAULT of the column it is set correctly, for example DEFAULT ' ' (has an empty space between the quotation marks) or DEFAULT 'VALOR DEFAULT'. The situation occurs only for '' (no space between quotation marks) and NULL where they seem to be understood as being the same thing and compared to MySql there is the difference. I know that Sqlite has its particularities, I just wanted to understand if it is possible to leave the column with the empty string and not being NULL.

sqlite sqlite3
  • 1

    I could not simulate this, the default should work: http://sqlfiddle.com/#! 5/e0afd/1

  • With your confirmation that it is working properly, I looked for other clients to analyze the situation and really everything is working normally. Which shows that only on my Client you are misdisplaying the information.

1 answer

1

After doing a lot of research after the comment from Daniel Mendes, I decided to install several clients for my database, and in comparative the problem is actually from Navcat. It displays the data incorrectly. The whole structure that the Sqlite mounted is working properly without any problem.

If someone uses this client (in the version I use at least) to work with Sqlite they will come across this situation.

Browser other questions tagged sqlite sqlite3

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