Create Table Primary key & Primary Index

Database

Create Table Primary key & Primary Index

Hi,

I am new to Teradata

I have used the following code for creating new table

CREATE SET TABLE TDUSER.contact ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT
(
contact_number INTEGER,
contact_name VARCHAR(30) NOT CASESPECIFIC NOT NULL,
area_code SMALLINT,
phone INTEGER NOT NULL,
extension INTEGER,
last_call_date DATE FORMAT 'YY/MM/DD' NOT NULL
constraint pk primary key (contact_number)
)
UNIQUE PRIMARY INDEX ( contact_number );

I got the following error

Failure 3707 Syntax error, expected something like a 'CHECK' keyword or
an 'UNIQUE' keyword between the 'key' keyword and '('.

Thanks
Mohan
2 REPLIES

Re: Create Table Primary key & Primary Index

you don't need to specify UNIQUE PRIMARY INDEX clause in CREATE TABLE statement, it'll automatically create UPI on PK Column(s).

CREATE SET TABLE TDUSER.contact ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT
(
contact_number INTEGER ,
contact_name VARCHAR(30) NOT CASESPECIFIC NOT NULL,
area_code SMALLINT,
phone INTEGER NOT NULL,
extension INTEGER,
last_call_date DATE FORMAT 'YY/MM/DD' NOT NULL,
constraint pk primary key (contact_number)
)
;

Re: Create Table Primary key & Primary Index

Thanks for your reply..

I have to mention explicitly the primary key is not null.

when I use your code it shows the following error:

Failure 3706 Syntax error: PRIMARY KEY Column(s) must be NOT NULL.
Statement# 1, Info =339

I have included the NOT NULL constraint in contact_number column

contact_number INTEGER NOT NULL ,

Thanks once again for your valuable solution...

Thanks,
Mohan