CODEWITHSUNDEEP
sqloracle-databaseoracle-sqldeveloper
lostinthebits
lostinthebits

Reputation: 661

add check constraint, get invalid data type error

Using Oracle SqlDeveloper

Table T has two columns ID (Number) Desc (VARCHAR2)

I try the following query to add the check:

ALTER TABLE T
ADD CONSTRAINT 100chk CHECK (ID BETWEEN 0 AND 100);

Error report: SQL Error: ORA-00902: invalid datatype 00902. 00000 - "invalid datatype" *Cause:
*Action:

The ID column is a number datatype - why isn't it letting me add that constraint?

Upvotes: 1

Views: 1077

Answers (1)

Ed Gibbs
Ed Gibbs

Reputation: 26333

Oracle is probably getting confused by your constraint name 100chk. Valid names start with a letter. You can put double quotes around an invalid name and Oracle will accept it, but this is generally considered to be a bad idea.

Try naming your constraint chk100 instead:

ALTER TABLE T ADD CONSTRAINT chk100 CHECK (ID BETWEEN 0 AND 100);

Oracle's object naming rules are here. This link is for 10.2 but the information holds for 11.x as well.

Upvotes: 7

Related Questions