SQL Server : understanding default values

Question

I am doing a database fundamentals course and 1 of the questions in the post-assessment is:

Default values must be set for NOT NULL rows. True|False

I am led to believe that the answer is true because I answered false and it was wrong, the issue is that I don't understand why it is true.

If the question was:

Default values must be set for NOT NULL columns when using ALTER TABLE to add a new column.

Then I know that is true and understand why. Am I misreading the question or am I misunderstanding something elsewhere?

Any help will be greatly appreciated. Lee.

Default values mean that when the value is NULL it will take the Default values insted, So is it true to set the column to NOT NULL? — Ilyes
– Ilyes, Commented Oct 28, 2017 at 9:02
I think that you’re right, let’s imagine we have a table with goods in stock for sale. What you can set as a default value for NOT NULL column PRICE? — Stepan Novikov
– Stepan Novikov, Commented Oct 28, 2017 at 9:03
Thanks for the replies. Say we have a firstName and lastName column, both are NOT NULL, what default value would you have there? Surely you'd let the server respond required value? — Lee Morgan
– Lee Morgan, Commented Oct 28, 2017 at 9:10
Sometimes you see bugs in the code - sometimes there are bugs in the questions ;) I'm quite sure you are right. — Ergaro
– Ergaro, Commented Oct 28, 2017 at 9:16
Yep, the question doesn’t even make sense, what is a NOT NULL row? — Martin Smith
– Martin Smith, Commented Oct 28, 2017 at 11:14

Gordon Linoff · Accepted Answer · 2017-10-28 12:07:08Z

2

You are correct. There is no requirement for a default definition when a column is declared not null. This is quite apparent if you look at the syntax diagram for create table:

<column_definition> ::=  
column_name <data_type>  
    . . . 
    [ CONSTRAINT constraint_name [ DEFAULT constant_expression ] ]  
    . . .  
    [ NULL | NOT NULL ]

If the syntax where required, it would look more like this:

<column_definition> ::=  
column_name <data_type>  
    . . . 
    [ CONSTRAINT constraint_name [ DEFAULT constant_expression ] ]  
    . . .  
    [ NULL | NOT NULL [CONSTRAINT constraint_name] DEFAULT constant_expression ]

I'm not even sure that a default definition is a good idea for not null columns. In many cases, you would want the insert to fail rather than inserting some artificial value.

The only relationship is when altering tables that have data:

If the new column does not allow null values and the table is not empty, a DEFAULT definition must be added with the new column, and the new column automatically loads with the default value in the new columns in each existing row.

This is rather obvious. The existing rows would be given a NULL value for the new column -- but the NOT NULL constraint disallows that, so a DEFAULT is needed.

edited Oct 28, 2017 at 12:07

answered Oct 28, 2017 at 11:17

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Lee Morgan Over a year ago

many thanks for your explanation. I emailed the people that set the questions and they have now amended the question from row to column and corrected the answer. Lee

Collectives™ on Stack Overflow

SQL Server : understanding default values

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related