1
Good evening, I created a table to test the sql server default:
I did a select to see if the default values were entered but you can see in the image that this did not occur. When does it enter these default values? I thought these values were entered when null or empty values were passed. Another problem is that the date entered was 1900 and this only happens in this case, because if I pass getDate() in Insert the date to save will be the current.
2
When you insert '' in the database , he NAY understands this as valor não informado and yes as string vazia . So in the varchar field it stores the empty string and in the date field it converts to 1900-01-01.
As you did not inform the order of the fields and which fields will be inserted in your Insert it follows the order of your table. Example:
If you do insert into [testes5] (nome) values ('josé') the date field will receive the getdate().
If you do insert into [testes5] (data) values ('2015-01-01') name field will receive the padrão.
or if you want to do Insert without informing the order and entered fields as tried can do
insert into [testes5] values (default,default)
Exactly, I noticed this. I posted an answer practically at the same time as you rs. I thought passing empty or null would cause the default value to be entered. Thanks!
Cool ! Completing the answer a little bit , a situation that the default might be quite useful is when you add a new column in your table that does not allow null and the table is already populated.
Got it. You could change a null column to not null using the default?
I think not, I would do the following, update table set column = default Where column is null . To then change the column not to accept nulll
0
CREATE TABLE [testes5] (
`id` integer PRIMARY KEY AUTOINCREMENT,
`nome` varchar(255) DEFAULT 'Padrão',
`data` date DEFAULT getdate()
)
INSERT INTO [testes5] (nome, data) VALUES (DEFAULT, DEFAULT)
SELECT * FROM [testes5]
If you specify a value without string or NULL, it will be recognized as blank or null instead of unspecified, causing the default value not to be inserted.
I don’t think you understand my question. My question was whether passed null or empty values registered the default value or in which cases these values would be entered. I saw that the default value is inserted if you do not pass the fields that contain the default in the Insert. Anyway, it was worth.
Exactly! ... Sorry for the misunderstanding, I forgot to explain in my reply. If you specify the column and value in INSERT (independent if it has no string or is NULL) SQL will recognize that as a blank value, that is: You specified a value for that column, so the default value will not be inserted. Updated response!
No problem, thanks a lot!
Browser other questions tagged sql sql-server default
You are not signed in. Login or sign up in order to post.
I saw that the default value is inserted if you do not pass the fields containing the default, in this case the name and date.
– HeyJoe