Archives of the TeradataForum

Message Posted: Mon, 24 Jan 2011 @ 13:41:09 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Hi,

Could anyone perhaps shed some light on this?

We would like to understand if there is any functional difference between:

a) Column-level check constraint on a column

b) Table-level check constraint on a column

Is there any difference in behavior in terms of:

1) Optimizer plans

2) Possible filtering of UNION-ALL views across similar tables (such as separate physical history tables under a UNION-ALL view)?

3) Other functionality

The manuals have been searched for some clue about this, to no avail.

Here is an example of the question above:

     CREATE MULTISET TABLE MyDB.MyTbl
         ,NO FALLBACK
         ,NO BEFORE JOURNAL
         ,NO AFTER JOURNAL
         ,FREESPACE = 0 PERCENT
         ,CHECKSUM = DEFAULT
          (
           Col1 DATE FORMAT 'YYYYMMDD' CHECK (   (Col1 >=  DATE '2008-01-01')
               AND (Col1 <  DATE '2009-01-01')   ) /* column-level */
          ,Col2 DATE FORMAT 'YYYYMMDD'
          ,Col3 INTEGER
          ,CHECK ( (Col2 >=  DATE '2008-01-01') AND  (Col2 <  DATE '2009-01-01') ) /* table level */
          )
     PRIMARY INDEX ( Col3 )
     ;

This shows that the constraints are viewed as somehow being different by the dictionary:

     select 'Col' Src ,DatabaseName ,TableName ,ColumnName CheckN ,ColCheck Chk from
     dbc.showcolchecks where databasename = 'MyDB'
     and tablename = 'MyTbl'
     UNION ALL
     select 'Tbl' Src ,DatabaseName ,TableName ,CheckName CheckN ,TblCheck Chk from
     dbc.showtblchecks where databasename = 'MyDB'
     and tablename = 'MyTbl'
     order by 1


     Src  DatabaseName     TableName    CheckN   Chk
     ---  ---------------  -----------  -------
     -----------------------------------------------------------------------
     Col  MyDB             MYTBL        Col1     CHECK ( (Col1 >=  DATE '2008-01-01')
     AND  (Col1 <  DATE '2009-01-01') )
     Tbl  MyDB             MYTBL        ?        CHECK ( (Col2 >=  DATE '2008-01-01')
     AND  (Col2 <  DATE '2009-01-01') )

Kind regards

Etienne Stieger

	<Prev	Next>		<<First	<Prev	Next>	Last>>