Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

invalid datatype error when creating primary key

532 views
Skip to first unread message

Steve Miller

unread,
Apr 2, 1997, 3:00:00 AM4/2/97
to

I'm trying to create some named primary keys on some tables in a 7.3.2.2
database and I'm getting the following error:

comex> alter table service.gp_cd2emp add constraint pk_gp_cd2emp1_c01
2 primary key (user_cd, emp_cd);
alter table service.gp_cd2emp add constraint pk_gp_cd2emp1_c01
*
ERROR at line 1:
ORA-00902: invalid datatype


I've tried adding quotes, parentheses, etc. and it just DOESN'T want to
work. I can add an unnamed constraint just fine:

comex> alter table service.gp_cd2emp add primary key (user_cd, emp_cd);

Table altered.

Anyone seen this before - Oracle support is working on the problem, but I
really need to push this project forward.

Any input appreciated!!

Steve


Jonathan G Gennick

unread,
Apr 2, 1997, 3:00:00 AM4/2/97
to

"Steve Miller" <smi...@gers.com> wrote:

>I'm trying to create some named primary keys on some tables in a 7.3.2.2
>database and I'm getting the following error:
>
>comex> alter table service.gp_cd2emp add constraint pk_gp_cd2emp1_c01
> 2 primary key (user_cd, emp_cd);
>alter table service.gp_cd2emp add constraint pk_gp_cd2emp1_c01

The syntax I usually use is:
alter table service.gp_cd2emp
add (constraint pk_gp_cd2emp1_c01 primary key (x,x)
);

Have you tried that?

jonathan
------------------------------------------------------------
Jonathan Gennick "Brighton the corner where you are."
jgen...@kpmg.com

Michael Ho

unread,
Apr 7, 1997, 3:00:00 AM4/7/97
to Steve Miller

Steve Miller wrote:
>
> I'm trying to create some named primary keys on some tables in a 7.3.2.2
> database and I'm getting the following error:
>
> comex> alter table service.gp_cd2emp add constraint pk_gp_cd2emp1_c01
> 2 primary key (user_cd, emp_cd);
> alter table service.gp_cd2emp add constraint pk_gp_cd2emp1_c01
> *
> ERROR at line 1:
> ORA-00902: invalid datatype
>
> I've tried adding quotes, parentheses, etc. and it just DOESN'T want to
> work. I can add an unnamed constraint just fine:
>
> comex> alter table service.gp_cd2emp add primary key (user_cd, emp_cd);
>
> Table altered.
>
> Anyone seen this before - Oracle support is working on the problem, but I
> really need to push this project forward.

Could it be because of the Unix Emulator which put some special
character
in the name without being seen by you ?

Michael Ho

unread,
Apr 7, 1997, 3:00:00 AM4/7/97
to Steve Miller

in the name without being seen by you ? Try do it in one line with
simple name.

L120bj

unread,
Apr 7, 1997, 3:00:00 AM4/7/97
to

This looks suspiciously like ORACLE is expecting v6 syntax for the alter
table statement. Is there a login.sql file in your home directory, or a
glogin.sql in $ORACLE_HOME/dbs which issues as SET COMPATABILITY v6
statement ?

Hope this helps
Rob
--------------------------------------------------------------------------
---------------------------------------------
Subject: invalid datatype error when creating primary key
From: "Steve Miller" <smi...@gers.com>
Date: 2 Apr 1997 17:58:41 GMT
Message-ID: <01bc3f8f$48ff5ee0$4f400580@pcws510>

I'm trying to create some named primary keys on some tables in a 7.3.2.2
database and I'm getting the following error:

comex> alter table service.gp_cd2emp add constraint pk_gp_cd2emp1_c01
2 primary key (user_cd, emp_cd);
alter table service.gp_cd2emp add constraint pk_gp_cd2emp1_c01
*
ERROR at line 1:
ORA-00902: invalid datatype


I've tried adding quotes, parentheses, etc. and it just DOESN'T want to
work. I can add an unnamed constraint just fine:

comex> alter table service.gp_cd2emp add primary key (user_cd, emp_cd);

Table altered.

Anyone seen this before - Oracle support is working on the problem, but I
really need to push this project forward.

Any input appreciated!!

Steve


Chidambaram

unread,
Apr 9, 1997, 3:00:00 AM4/9/97
to

Check whether u have defined user_cd or emp_cd as just number instead of
number(6) or number(x). This causes problem for us once.
Chida.

In article <3347CD...@hk.super.net>, inf...@hk.super.net says...


>
>Steve Miller wrote:
>>
>> I'm trying to create some named primary keys on some tables in a 7.3.2.2
>> database and I'm getting the following error:
>>
>> comex> alter table service.gp_cd2emp add constraint pk_gp_cd2emp1_c01
>> 2 primary key (user_cd, emp_cd);
>> alter table service.gp_cd2emp add constraint pk_gp_cd2emp1_c01
>> *
>> ERROR at line 1:
>> ORA-00902: invalid datatype
>>
>> I've tried adding quotes, parentheses, etc. and it just DOESN'T want to
>> work. I can add an unnamed constraint just fine:
>>
>> comex> alter table service.gp_cd2emp add primary key (user_cd, emp_cd);
>>
>> Table altered.
>>
>> Anyone seen this before - Oracle support is working on the problem, but I
>> really need to push this project forward.
>

Dan Townsend

unread,
Apr 14, 1997, 3:00:00 AM4/14/97
to

Check the compatibility of the database. Databases that were originally
Oracle6 and were upgraded to Oracle7 often have compatibility set to V6.
You get this error message using Oracle7 syntax against a database that
is running with compatibility V6. To correct, use V6 syntax, or (in
SQL*Plus) use SET COMPATIBILITY V7. Better yet, SET COMPATIBILITY
NATIVE, which will then match the compatibility of the database.
+-----------------------------------------------------
| Dan Townsend, Supervising Database Architect
| EBMUD Enterprise Object Designer
| mailto:town...@ebmud.com
+-----------------------------------------------------
0 new messages