This page is part of the book SQL-99 Complete, Really, by Peter Gulutzan & Trudy Pelzer. The authors have graciously allowed us to reproduce the contents of the book here. Because the book is about the SQL-99 standard, the contents of this and other pages in the book may not directly apply to MariaDB. Use the navigation bar to navigate the book.

A <default clause> defines the default value for a Column, a Domain or an attribute of a UDT. The required syntax for a <default clause> is:

<default clause> ::= 
DEFAULT default value 

   default value ::=
   <literal> | 
   USER | 
   CURRENT_USER | 
   SESSION_USER | 
   SYSTEM_USER | 
   CURRENT_PATH | 
   CURRENT_DATE | 
   CURRENT_TIME[(p)] | 
   CURRENT_TIMESTAMP[(p)] | 
   LOCALTIME[(p)] | 
   LOCALTIMESTAMP[(p)] | 
   ARRAY[] | 
   ARRAY??(??) | 
   NULL

The default value of an Object is the data value that will be inserted into the Object whenever it is the target of an INSERT statement that does not provide an explicit data value for that Object. If the definition of an Object does not include a <default clause>, no default value is assigned to it so when the Object is the target of an INSERT statement that does not provide an explicit data value for it, your DBMS will INSERT a null value. (If the Object doesn't allow nulls, the INSERT will, of course, fail.) The <data type> of a default value must match the Object's <data type> (that is, the default value and the Object's <data type> must be mutually assignable).

  • If the <data type> of an Object is a <reference type>, the <default clause> must be "DEFAULT NULL".
  • If the <data type> of an Object is a <collection type>, the <default clause> must be "DEFAULT NULL" or "DEFAULT ARRAY[]" or "DEFAULT ARRAY??(??)" or "DEFAULT <literal>".

If a <default clause> is "DEFAULT <literal>", the value represented by the <literal> is the target Object's default value. Here are some examples of <default clause>s with a <literal> as the default value:

CREATE DOMAIN domain_1 AS NCHAR(5) DEFAULT N'sammy'; 

CREATE TABLE Table_1 (column_1 VARCHAR(6) DEFAULT 'bob'); 

CREATE DOMAIN domain_1 AS BIT VARYING(4) DEFAULT B'0010'; 

CREATE TABLE Table_1 (column_1 BIT(16) DEFAULT X'4E2C');
  -- If the target Object has a BIT <data type> and the length of your <literal> is less than the defined length of the Object, your DBMS will return the SQLSTATE warning 01008 "warning-implicit zero-bit padding".

CREATE DOMAIN domain_1 AS SMALLINT DEFAULT 100; 

CREATE TABLE Table_1 (column_1 REAL DEFAULT 15000); 

CREATE DOMAIN domain_1 AS DATE DEFAULT DATE '1994-07-15'; 

CREATE TABLE Table_1 ( 
  column_1 INTERVAL MONTH DEFAULT INTERVAL '03' MONTH); 

CREATE DOMAIN domain_1 AS BOOLEAN DEFAULT FALSE

If a <default clause> is "DEFAULT USER", "DEFAULT CURRENT_USER", "DEFAULT SESSION_USER" or "DEFAULT SYSTEM_USER", the value returned by the function is the target Object's default value. In this case, the target Object must have a character string <data type> with a defined length of at least 128 characters and must belong to the SQL_TEXT Character set. Here are some examples of <default clause>s with a <niladic user function> as the default value:

CREATE DOMAIN domain_1 AS 
  CHAR(128) CHARACTER SET SQL_TEXT DEFAULT CURRENT_USER;

CREATE TABLE Table_1 ( 
  column_1 VARCHAR(256) CHARACTER SET SQL_TEXT DEFAULT SESSION_USER;

If a <default clause> is "DEFAULT CURRENT_PATH", the value returned by the function is the target Object's default value. In this case, the target Object must have a character string <data type> with a defined length of at least 1031 characters and must belong to the SQL_TEXT Character set. Here are some examples of <default clause>s with CURRENT_PATH as the default value:

CREATE DOMAIN domain_1 AS 
  CHAR(1031) CHARACTER SET SQL_TEXT DEFAULT CURRENT_PATH;

CREATE TABLE Table_1 ( 
  column_1 VARCHAR(2000) CHARACTER SET SQL_TEXT DEFAULT CURRENT_PATH;

If a <default clause> is "DEFAULT CURRENT_DATE", "DEFAULT CURRENT_TIME[(p)]", "DEFAULT CURRENT_TIMESTAMP[(p)]", "DEFAULT LOCALTIME[(p)]", or "DEFAULT LOCALTIMESTAMP[(p)]", the value returned by the function is the target Object's default value. In this case, the target Object must have a datetime <data type> that matches the function's <data type>. Here are some examples of <default clause>s with a <datetime value function> as the default value:

CREATE DOMAIN domain_1 AS 
  DATE DEFAULT CURRENT_DATE;

CREATE TABLE Table_1 ( 
  column_1 TIME WITH TIME ZONE DEFAULT CURRENT_TIME;

CREATE DOMAIN domain_1 AS 
  TIMESTAMP(4) DEFAULT CURRENT_TIMESTAMP(4);

CREATE TABLE Table_1 ( 
  column_1 TIME(4) DEFAULT LOCALTIME(4);

If a <default clause> is "DEFAULT ARRAY[]" or "DEFAULT ARRAY??(??)", an empty array value is the target <collection type>'s default value. Here is an example of a <default clause> with an empty array as the default value:

CREATE DOMAIN domain_1 AS 
  INT ARRAY[3] DEFAULT ARRAY[];

If a <default clause> is "DEFAULT NULL", the null value is the target Object's default value. (The Object can't, of course, have a NOT NULL Constraint.) Here is an example of a <default clause> with a null value as the default value:

CREATE TABLE Table_1 ( 
  column_1 CHAR(15) DEFAULT NULL;

[Obscure Rule] If a <default clause> that is part of an SQL-Schema statement defines a default value that can't be represented in INFORMATION_SCHEMA without truncation, your DBMS will return the SQLSTATE warning 0100B "warning-default value too long for information schema".

If you want to restrict your code to Core SQL, don't use "DEFAULT CURRENT_PATH" when defining a <default clause>.

Comments

Comments loading...