CODEWITHSUNDEEP
oracle-databaseconstraintsddlora-00907
mLstudent33
mLstudent33

Reputation: 1175

oracle sql check constraint for number to_char

This is a homework problem that I'm inputting on oracle live sql website:

Create table student (regno number (6), mark number (3) constraint b check (mark >=0 and
mark <=100));
Alter table student add constraint b2 check (length(regno<=4));

It keeps throwing a "missing right parenthesis" error on the second line to Alter. I read elsewhere this is a generic error for syntax but for the life of me, even if I copy and paste the code from the source material into the SQL worksheet or retype it about 20 times now, I keep getting the error.

I also tried casting to char as regno is a number. 

Alter table student add constraint b2 check (length(to_char(regno)<=4));

But I get the same error.

Upvotes: 0

Views: 525

Answers (1)

Popeye
Popeye

Reputation: 35900

You comparison operator(<=) should be outside the length function:

SQL> CREATE TABLE STUDENT (
  2      REGNO   NUMBER(6),
  3      MARK    NUMBER(3)
  4          CONSTRAINT B CHECK ( MARK >= 0
  5                               AND MARK <= 100 )
  6  );

Table created.

SQL> -- Solution of the question
SQL> ALTER TABLE STUDENT
  2      ADD CONSTRAINT B2 CHECK ( LENGTH(REGNO) <= 4 );

Table altered.

SQL>

One suggestion, If you want to restrict the REGNO to only 4 digits then convert the data type of REGNO as NUMBER(4)

Cheers!!

Upvotes: 2

Related Questions