Scripting Primary Keys

Database
Enthusiast

Scripting Primary Keys

I have noticed that while creating a table, I can create Primary Keys using the following syntax :

CONSTRAINT name PRIMARY KEY (column1, column2....)

However if I pull up the SQL for table creation after the initial create, this becomes

UNIQUE PRIMARY INDEX name (column1, column2...)

and this negates all Primary Key constraints that were initially set if I run a DROP/CREATE. Is there any way to script multiple primary keys with a UPI and have it stick after the initial create?
2 REPLIES
Junior Contributor

Re: Scripting Primary Keys

Retaining the PK definition was implemented a few month ago, but i don't remember which release/patch level.
I have to admit that i didn't like it, because it changed the output of SHOW TABLE without prior notice (I'd prefer a flag to control this behaviour).

CREATE TABLE dropme(a INT NOT NULL, CONSTRAINT pk PRIMARY KEY(a));

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

BTEQ -- Enter your SQL request or BTEQ command:

SHOW TABLE dropme;

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

-------------------------------------------------------------------
CREATE SET TABLE TERADATA_EDUCATION.dropme ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT
(
a INTEGER NOT NULL,
CONSTRAINT pk PRIMARY KEY ( a ))
;

When your release doesn't provide this feature you still can get info about which index implements the actual PK you defined in CREATE TABLE:
- dbc.TablesV.PrimaryKeyIndexId
- dbc.Indices.IndexType = 'K'

Dieter
Enthusiast

Re: Scripting Primary Keys

Thank you for your reply Dieter. I have found the issue is what I expect to get back from my query and what i actually get back. My company is contacting Teradata about the issue and requesting a patch to resolve it.

Matt