Default values ​​in Column - SQL Table

We have seen that if when inserting records a value is not specified for a field that admits null values, "null" is automatically entered. This value is called the default or default value.

A default value is inserted when it is not present when entering a record.

For fields of any type not declared "not null", that is, that admit null values, the default value is "null". For fields declared "not null", there is no default value, unless explicitly declared with the "default" clause.

We can set default values ​​for the fields when we create the table. For this we use "default" when defining the field. For example, we want the default value of the "author" field of the "books" table to be "Unknown" and the default value of the "quantity" field to be "0":

 create table books (

  title varchar2 (40) not null,

  author varchar2 (30) default 'Unknown' not null, 

  editorial varchar2 (20),

  price number (5,2),

  quantity number (3) default 0

 );

If when entering a new record we omit the values ​​for the "author" and "quantity" fields, Oracle will insert the default values; in "author" it will put "Unknown" and in quantity "0".

So, if when defining the field we make a value explicit through the "default" clause, that will be the default value.

The "default" clause must come before "not null" (if it exists), otherwise an error message appears.

To see if the fields of the "books" table have defined default values ​​and what they are, we can perform the following query:

 select column_name, nullable, data_default

  from user_tab_columns where TABLE_NAME = 'BOOKS';

It shows a row for each field, in the column "data_default" the default value appears (if it has it), in the column "nullable" it appears "N" if the field is not defined "not null" and "Y" if it admits "null" values.

You can also use "default" to give the default value to fields in "insert" statements, for example:

 insert into books (title, author, publisher, price, quantity)

  values ​​('Puss in Boots', default, default, default, 100);

So, the "default" clause allows you to specify the default value of a field. If not specified, the default value is "null", provided that the field has not been declared "not null".

The fields for which no values ​​are entered in an "insert" will take the default values:

• if it allows null values ​​and has no "default" clause, it will store "null";
• if it has a "default" clause (whether or not it allows null values), the value defined as default;
• if it is explicitly declared "not null" and has no "default" value, there is no default value, so it will cause an error and the "insert" will not be executed.

A field can only have one default value. A table can have all its fields with default values. That a field has a default value does not mean that it does not allow null values, it may or may not support them.

A field defined as a primary key accepts a "default" value, but it does not make sense since the default value can only be entered once; If you try to enter when another record already has it stored, an error message will appear indicating that an attempt is made to duplicate the key.