Home Page for the TeradataForum
 

Archives of the TeradataForum

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


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


Subj:   Difference between Column-level vs Table-level
 
From:   Stieger, Etienne

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>>
 
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 27 Dec 2016