Archives of the TeradataForum

Message Posted: Wed, 05 Mar 2008 @ 11:30:10 GMT


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

Subj:		Re: Can I add composite primary key on table?

From:		Victor Sokovin

Create table VWPRODUCT_LEAF as (select * from vwproduct_leaf_rl) with no data;

          > --> VWPRODUCT_LEAF_RL is a view.
          > --> VWPRODUCT_LEAF table created with all columns are with NULL.

By default first column(i.e. item_ID) taken as a primary key. But I need to make composite primary key(i.e. item_id,X,Y) on VWPRDODUCT_LEAF.

This is a bit confusing as views don't have primary keys so you should not get a default primary key created.

I think what is created by default is the primary *index*.

When I try to Alter table with below statement

          > ALTER TABLE VWPRODUCT_LEAF_RPT ADD CONSTRAINT PK_VWPRODUCT_LEAF_RPT
          > PRIMARY KEY  ( ITEM_ID, x,y);

          > ERROR: 5323: Primary key column 'ITEM_ID' must be NOT NULL

When I try to alter table with below statement for make ITEM_ID as not null

          > ALTER TABLE VWPRODUCT_LEAF_RPT ADD ITEM_ID NOT NULL;

          > ERROR: ITEM_ID is a Primary Key cannot modify

Does it say Key or Index here?

You need the NOT NULL constraints on all PK columns. I think you can add it explicitly in the CREATE TABLE ... AS ... WITH NO DATA but that would require explicit listing of the columns so the value of this statement becomes limited and I would rather take the definition of the view and modify it to the explicit definition of the table, with all NOT NULL clauses and the exact PK definition you are trying to build.

Victor


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

Attachments

Library

Quick Reference