Utilizing Default Values for a Table Teradata

A default value control phrase determines the action to be taken when you do not supply a value for a field. Default value control phrases are only valid when used with the columns defined in the CREATE TABLE and ALTER TABLE statements as well as parameters defined in the CREATE MACRO and REPLACE MACRO statement.

A default value control phrase determines the action to be taken when you do not supply a value for a field. Instead of placing a NULL the system will place the default value listed in the CREATE or ALTER table command. The following rules and guidelines apply to default value control phrases:

  • Fields must be defined in CREATE TABLE and ALTER TABLE statements
  • Parameters must be defined in CREATE MACRO and REPLACE MACRO statements
  • Default value controls are not effective for views and expressions.
  • The normal default value for a field is null unless you specify NOT NULL.

Teradata allows you to specify default values when creating a table. As seen earlier, the keyword DEFAULT VALUES can be used in an INSERT. When this is done, any columns that have default values defined in the CREATE TABLE statement use the default instead of a NULL. All columns without a DEFAULT phrase defined for them contain a NULL. However, if a column does not have a DEFAULT defined and has an attribute of NOT NULL, an error is returned and the insert fails.

Below is an example of a table with defaults.

We have now created an empty table called Dept_Defaults. We will now insert data in our next example.

INSERT INTO TOMC.DEPT_DEFAULTS DEFAULT VALUES;

We will now select from the DEPT_DEFAULTS table.

SELECT * from TOMC. DEPT_DEFAULTS; 1 Row ReturnedUtilizing Default Values for a Table

Face Book Twitter Google Plus Instagram Youtube Linkedin Myspace Pinterest Soundcloud Wikipedia

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Teradata Topics