Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL Column definition : default value and not null redundant?

I've seen many times the following syntax which defines a column in a create/alter DDL statement:

ALTER TABLE tbl ADD COLUMN col VARCHAR(20) NOT NULL DEFAULT "MyDefault" 

The question is: since a default value is specified, is it necessary to also specify that the column should not accept NULLs ? In other words, doesn't DEFAULT render NOT NULL redundant ?

like image 799
Razvan Avatar asked Aug 08 '12 10:08

Razvan


People also ask

Can default and not null be used together?

Yes, adding a column with NOT NULL and a default doesn't actually write the values to all the rows at the time of the alter, so it is no longer a size-of-data operation. When you select from the table, the columns are actually materialized from sys.

Are SQL columns NULL by default?

By default, the columns are able to hold NULL values. A NOT NULL constraint in SQL is used to prevent inserting NULL values into the specified column, considering it as a not accepted value for that column.

IS NOT NULL primary key redundant?

It may be redundant, but I prefer this method of creating a primary key (or Identity column). It shows that the person making the table understands and intends the column to be NOT NULL as well as the Primary Key for the table. Show activity on this post. It would be the equivalen.


1 Answers

DEFAULT is the value that will be inserted in the absence of an explicit value in an insert / update statement. Lets assume, your DDL did not have the NOT NULL constraint:

ALTER TABLE tbl ADD COLUMN col VARCHAR(20) DEFAULT 'MyDefault' 

Then you could issue these statements

-- 1. This will insert 'MyDefault' into tbl.col INSERT INTO tbl (A, B) VALUES (NULL, NULL);  -- 2. This will insert 'MyDefault' into tbl.col INSERT INTO tbl (A, B, col) VALUES (NULL, NULL, DEFAULT);  -- 3. This will insert 'MyDefault' into tbl.col INSERT INTO tbl (A, B, col) DEFAULT VALUES;  -- 4. This will insert NULL into tbl.col INSERT INTO tbl (A, B, col) VALUES (NULL, NULL, NULL); 

Alternatively, you can also use DEFAULT in UPDATE statements, according to the SQL-1992 standard:

-- 5. This will update 'MyDefault' into tbl.col UPDATE tbl SET col = DEFAULT;  -- 6. This will update NULL into tbl.col UPDATE tbl SET col = NULL; 

Note, not all databases support all of these SQL standard syntaxes. Adding the NOT NULL constraint will cause an error with statements 4, 6, while 1-3, 5 are still valid statements. So to answer your question: No, they're not redundant.

like image 157
Lukas Eder Avatar answered Sep 26 '22 09:09

Lukas Eder