CODEWITHSUNDEEP
sqlsql-server
Lee Morgan
Lee Morgan

Reputation: 112

SQL Server : understanding default values

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.

Upvotes: 3

Views: 106

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271023

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.

Upvotes: 2

Related Questions