SQL Column definition default value and not null redundant

Question

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

User · Answer

I would say not   If the column does accept null values  then there s nothing to stop you inserting a null value into the field  As far as I m aware  the default value only applies on creation of a new row   With not null set  then you can t insert a null value into the field as it ll throw an error   Think of it as a fail safe mechanism to prevent nulls

User · Answer

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

User · Answer

In case of Oracle since 12c you have DEFAULT ON NULL which implies a NOT NULL constraint  ALTER TABLE tbl ADD  col VARCHAR 20  DEFAULT ON NULL  MyDefault     ALTER TABLE  ON NULL If you specify the ON NULL clause  then Oracle Database assigns the DEFAULT column value when a subsequent INSERT statement attempts to assign a value that evaluates to NULL  When you specify ON NULL  the NOT NULL constraint and NOT DEFERRABLE constraint state are implicitly specified  If you specify an inline constraint that conflicts with NOT NULL and NOT DEFERRABLE  then an error is raised

User · Answer

My SQL teacher said that if you specify both a DEFAULT value and NOT NULLor NULL  DEFAULT should always be expressed before NOT NULL or NULL   Like this    ALTER TABLE tbl ADD COLUMN col VARCHAR 20   DEFAULT  MyDefault  NOT NULL  ALTER TABLE tbl ADD COLUMN col VARCHAR 20   DEFAULT  MyDefault  NULL

User · Answer

In other words  doesn t DEFAULT render NOT NULL redundant    No  it is not redundant  To extended accepted answer  For column col which is nullable awe can insert NULL even when DEFAULT is defined  CREATE TABLE t id INT PRIMARY KEY  col INT DEFAULT 10    -- we just inserted NULL into column with DEFAULT INSERT INTO t id  col  VALUES 1  NULL     ----- ------    ID    COL     ----- ------      1   null    ----- ------   Oracle introduced additional syntax for such scenario to overide explicit NULL with default DEFAULT ON NULL  CREATE TABLE t2 id INT PRIMARY KEY  col INT DEFAULT ON NULL 10   -- same as --CREATE TABLE t2 id INT PRIMARY KEY  col INT DEFAULT ON NULL 10 NOT NULL     INSERT INTO t2 id  col  VALUES 1  NULL     ----- -----    ID    COL    ----- -----     1     10    ----- -----   Here we tried to insert NULL but get default instead  db lt  gt fiddle demo  ON NULL  If you specify the ON NULL clause  then Oracle Database assigns the DEFAULT column value when a subsequent INSERT statement attempts to assign a value that evaluates to NULL  When you specify ON NULL  the NOT NULL constraint and NOT DEFERRABLE constraint state are implicitly specified

User · Answer

Even with a default value  you can always override the column data with null   The NOT NULL restriction won t let you update that row after it was created with null value

[sql] SQL Column definition : default value and not null redundant?

Examples related to sql

Examples related to default-value

Examples related to ddl

Examples related to notnull