Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Thu, 17 May 2001 @ 06:26:09 GMT


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


Subj:   Primary Key's vs indexes
 
From:   Mike Philips

Hi all,

I am using the 'PRIMARY KEY' syntax in CREATE TABLE DDL's, meaning that this is a uniqueness constraint, NOT a primary INDEX, specifically where there is a primary INDEX defined. (N.B. generated from ERwin). It appears that Teradata takes it upon itself to create a unique secondary index: eg.

Using BTEQWin (and presumably BTEQ on UNIX):

CREATE TABLE T1 (
       F1            CHAR(4) NOT NULL TITLE '%AttName',
       F2               INTEGER NOT NULL,
       F3       CHAR(2) NOT NULL,
       F4           CHAR(61),
       F5        SMALLINT,
       PRIMARY KEY (F1,F2,F3)
)
       PRIMARY INDEX XIE1T1 (F1,F2);

This is the SHOW TABLE output:

CREATE SET TABLE edw.T1 ,NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL
     (
      F1 CHAR(4) CHARACTER SET LATIN NOT CASESPECIFIC TITLE '%AttName'
NOT NULL,
      F2 INTEGER NOT NULL,
      F3 CHAR(2) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
      F4 CHAR(61) CHARACTER SET LATIN NOT CASESPECIFIC,
      F5 SMALLINT )
PRIMARY INDEX XIE1T1 ( F1 ,F2 )
UNIQUE INDEX ( F1 ,F2 ,F3 );

Also, under QueryManager, it rejects the DDL syntax with:

3516: More than one primary index or primary key specified !!!

Also (2), is a uniqueness constraint in effect (eg in MultiLoad UV rejections) from the PRIMARY KEY syntax (V2R3)?

Any comments here please?

Many thanks

Mike Philips

Professional Services / Pre-Sales
National Data Systems (NDS)
(NCR exclusive distributor)
South Africa



     
  <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