Archives of the TeradataForum
Message Posted: Wed, 05 Mar 2008 @ 11:30:10 GMT
| 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
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.