Validates that specified column is NOT nullable for the existing table, in other words it checks that NULL values cannot be stored in the column. Assert will fail if column is nullable.

Arguments

  • v_ObjectName – table name. Supports four part object name. Variable Character(255) in Oracle and Netezza, SYSNAME in SQL Server
  • v_ColumnName – name of the column, Variable Character(255) in Oracle and Netezza, SYSNAME in SQL Server
  • v_UserMessage – message to report when assertion fails, Variable Character(255) in Oracle and Netezza, NVARCHAR(MAX) in SQL Server

Note: Oracle uses DBTD_ASSERT_COL_NOT_NULLABLE name, which is also supported by Netezza and SQL Server after installing compatibility pack. 

Examples

SQL Server 

      EXEC DBTD_ASSERT_COLUMN_IS_NOT_NULLABLE 
          'myTable',
          'primaryKey',
          'Column should not allow null values';

      or

      EXEC DBTD_ASSERT_COL_NOT_NULLABLE 'myTable', 'primaryKey', 'column is nullable, when it should not';

      or

CREATE PROCEDURE UT_Infrastructure_CreditQuality_Columns
AS
BEGIN
     EXEC DBTD_UNIT_TEST 'Infrastructure';

     EXEC DBTD_ASSERT_COL_NOT_NULLABLE 'CreditQuality', 'AAA', 'should contain values';
END;

 

Oracle 

      DBTD_ASSERT_COL_NOT_NULLABLE ('Product', 'ProdutID', 'Column should not allow null values');

Netezza

      CALL DBTD_ASSERT_COLUMN_IS_NOT_NULLABLE ('Cat', 'Weight', 'Column should not allow null values');

      or  

      CALL DBTD_ASSERT_COL_NOT_NULLABLE ('Cat', 'Weight', 'Column should not allow null values');

 

See Also