0

I already have my tables full of data, I am trying to add a constraint to only allow letters to be entered into the attribute I have named as studentfname. I have tried various different REGEXP but cannot seem to get any to work without getting an error when inserting them.

Im not sure if its a syntax error or what!

Edit: Here is what I have tried and the error I am getting:

ALTER TABLE STUDENT
ADD CONSTRAINT
check_name
 CHECK(regexp_like(studentfname,'^[A-Za-z''-]+$'));

Error:

QL Error: ORA-02293: cannot validate (JEIGH7.CHECK_NAME) - check constraint violated
02293. 00000 - "cannot validate (%s.%s) - check constraint violated"
*Cause:    an alter table operation tried to validate a check constraint to
           populated table that had nocomplying values.
*Action:   Obvious
Improve this question
5
  • 1
    Please edit your question to show us what you have tried, including the full statement you used to add the constraint, plus the error you get creating the constraint or inserting data. Do you have existing data that does not match the pattern? If so what do you want to do - fix that data or have the constraint ignore it? Commented Apr 18, 2016 at 18:32
  • Hi there, I have checked all existing data and none of which doesn't match the pattern. Commented Apr 18, 2016 at 18:36
  • 3
    Apparently there is at least one row that does not validate. I believe Oracle more then your claim that there is no invalid data. Commented Apr 18, 2016 at 18:39
  • I believe it was the expression and I have managed to solve it using CHECK(regexp_like(studentfname,'[a-zA-Z]')); Commented Apr 18, 2016 at 18:39
  • That expression checks to see that there is at least one letter in the studentfname column. It doesn't prevent you from having as many additional non-letter characters as you would like. Commented Apr 18, 2016 at 18:55

1 Answer 1

Reset to default
3

Apparently you can try to run this below query to check if any non integral data resides in the above mentioned column which is preventing you from applying a check constraints. Once you get the output from this query try UPDATING/DELETING the row and then apply the constraint. Hope this helps.

SELECT studentfname nm FROM STUDENT a
WHERE NOT regexp_like(a.nm,'^[A-Za-z''-]+$');
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Your answer is good (and I upvoted), but I'm not sure why you're using a subquery. It's not necessary and might confuse the OP.
Ooops my bad. I was thinking something else while posting. I will rectify it. Thanks for the catch.
Thank you, turned out there were some spaces after data that had been entered, all sorted.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.