Archives of the TeradataForum

Message Posted: Tue, 26 May 2009 @ 11:46:13 GMT

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

Hi Mandasmitha,

Following is the first mail on this subject from "Anomy.Anom":

Here Anomy.Anom was creating a new table from "existing table".

Your question is different.

The first method you mentioned is out of context as you are just creating table using a standard DDL statement and NOT from existing table.

2nd and 3rd methods in your question are pretty much same. Still, if you ask me, I would go for 3rd. As far as "passing on the constraints" is concerned, both these methods CANNOT pass on constraints from existing table to the new table.

Example:

     CREATE TABLE test1 (a INTEGER NOT NULL, b CHAR(4) NOT NULL)
     PRIMARY INDEX (a)
     UNIQUE INDEX (b);

     CREATE TABLE test2 as (SELECT a, b FROM test1) WITH [NO] DATA;

     SHOW TABLE test2;

You will get:

     CREATE SET TABLE test2, FALLBACK,
          NO BEFORE JOURNAL,
          NO AFTER JOURNAL,
          CHECKSUM = DEFAULT
          (
           a INTEGER,
           b CHAR(4) CHARACTER SET LATIN NOT CASESPECIFIC)
     PRIMARY INDEX (a);

Note here that there is no "UNIQUE INDEX" and no "NOT NULL" defined in the new table CREATE statement. But both these constraints do exist in the existing table.

Thanks

Lakhwant

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