All Forums Database
MattACG 5 posts Joined 04/11
14 Apr 2011
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?

dnoeth 4628 posts Joined 11/04
15 Apr 2011

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

Dieter

MattACG 5 posts Joined 04/11
20 Apr 2011

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

You must sign in to leave a comment.