Dropping Primary Composite Key

Database
Enthusiast

Dropping Primary Composite Key

Hi All,

I have a table that has a composite key defined on 2 columns.

These column are not a part of primary index.

The table contains data.

I am trying to drop primary key using the following command and getting a systax error :

alter table person_mst drop primary key (id,name);

alter table person_mst drop primary key ;

 ALTER TABLE Failed. 3707:  Syntax error, expected something like a 'CHECK' keyword between the 'drop' keyword and the 'primary' keyword. 

I have used Sybase earlier and the above command works well.

Can you suggest what is wrong in the above mentioned command set.

5 REPLIES
Enthusiast

Re: Dropping Primary Composite Key

You can't drop a Primary Index in Teradata.

Your initial assertion that its a primary key but not part of the PI cannot be correct.  If you specify Primary Key in your Teradata DDL, TD will create it as a unique primary index, so your columns are indeed part of the PI.

If  "Primary Key" was specified in your DDL, the only option you have is to create a new table with duplicate columns and the desired PI, insert from old to new, validate the data, drop old table and rename the new table to the old table.

Enthusiast

Re: Dropping Primary Composite Key

Hi,

Thanks for your inputs.

However, i understand that if primary key is not a part of primary index , then the primary keys are created as unique secondary indexes.

I was able to drop the primary key by drop index command.

Works fine.

Enthusiast

Re: Dropping Primary Composite Key

Primary Keys and USI's are different animals if you try to run the DDL below code, it fails with "More than one primary index of primary key specified".  Teradata will create a Unique Primary Index on the table consisting of (col1,col2) if you remove the Primary Index specification for this DDL.  You can't do both.  You will only get "Primary Key" or "Primary Index" if the columns are different. Teradata won't create Primary Keys as USI's, you have to specify the USI in a create index statement, which is commonly done to maintain uniqueness on the table when the primary access columns differ from the columns that define uniqueness.

CREATE TABLE dbname.pk_test
, NO BEFORE JOURNAL, NO AFTER JOURNAL, NO FALLBACK, CHECKSUM = DEFAULT
(col1 INTEGER NOT NULL PRIMARY KEY
,col2 INTEGER NOT NULL PRIMARY KEY
,col3 INTEGER NOT NULL
)
PRIMARY INDEX (col3);

Junior Contributor

Re: Dropping Primary Composite Key

Of course you can create a PI and a PK on the same table, your example only fails because you try to create two PKs.

CREATE TABLE pk_test
(col1 INTEGER NOT NULL
,col2 INTEGER NOT NULL
,col3 INTEGER NOT NULL
,PRIMARY KEY(col1,col2)
)
PRIMARY INDEX (col3);

*** Table has been created.
*** Total elapsed time was 1 second.

SHOW TABLE pk_test;

*** Text of DDL statement returned.
*** Total elapsed time was 1 second.

CREATE SET TABLEpk_test ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT,
DEFAULT MERGEBLOCKRATIO
(
col1 INTEGER NOT NULL,
col2 INTEGER NOT NULL,
col3 INTEGER NOT NULL,
PRIMARY KEY ( col1 ,col2 ))
PRIMARY INDEX ( col3 );

SEL IndexNumber, IndexType, UniqueFlag FROM dbc.IndicesV
WHERE DatabaseName = DATABASE AND TableName = 'pk_test'
ORDER BY IndexNumber, ColumnPosition;

*** Query completed. 3 rows found. 3 columns returned.
*** Total elapsed time was 1 second.

IndexNumber IndexType UniqueFlag
----------- --------- ----------
1 P N
4 K Y
4 K Y

And a PK can be dropped if it's not the UPI of that table), but only when it's a named constraint, just don't ask me why :-)

CREATE TABLE pk_test
(col1 INTEGER NOT NULL
,col2 INTEGER NOT NULL
,col3 INTEGER NOT NULL
,CONSTRAINT pk PRIMARY KEY(col1,col2)
)
PRIMARY INDEX (col3);

ALTER TABLE pk_test DROP CONSTRAINT pk;

Dieter

Enthusiast

Re: Dropping Primary Composite Key

Thanks for the inputs everyone ..:)