Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Getting ORA-00902 when adding constraint with no name.

Hello everyone

I have the following table called CUSTOMERS, using 21C Xe

Columns 
NAME            DATA TYPE            NULL  DEFAULT    COMMENTS
 CUSTOMER_ID    NUMBER(38,0)         No               
 CUSTOMER_NAME  VARCHAR2(128 BYTE)   Yes              
 REGION_ID      NUMBER(38,0)         Yes              
 COUNTY         VARCHAR2(26 BYTE)    Yes              
 AREA           VARCHAR2(26 BYTE)    Yes              
 COUNTRY        VARCHAR2(26 BYTE)    Yes              
 NO_OF_STORES   NUMBER(3,0)          Yes              

Right now, the only constraint it has got is the NOT NULL constraint on the CUSTOMER_ID

If I try to add primary key constraint to CUSTOMER_ID column

alter table customers
    add constraint primary key (customer_id);

I get this error

Error starting at line : 3 in command -
alter table customers
add constraint primary key (customer_id)
Error report -
ORA-00902: invalid datatype
00902. 00000 - "invalid datatype"
*Cause:
*Action:

but if I add a random constraint name, the code works fine.

alter table customers
    add constraint constraint_name primary key (customer_id);

do you know why this might be case, I thought the system will generate a system constraint name if the constraint is unnamed, so why am I getting that 00902 invalid datatype error when adding contraint name works fine and has nothing to do with change datatype?

thanks.

This post has been answered by mathguy on Oct 7 2023
Jump to Answer
Comments
Post Details
Added on Oct 7 2023
3 comments
787 views