Add fraction constraint to a column in oracle

I am using oracle 10gr2. I am trying to enforce a constraint on a column called "score" such that only fractions can be entered.

More specifically, a the format should contain one digit in the numerator and one digit in the denominator, such tha开发者_如何学Got a user can only enter a fraction such as 3/4,2/5,or 7/8. The column only accepts numbers as the input. Can anyone show me the SQL to use?

If I understand correctly, I think the proper way to do this is to store the data in two columns. This especially makes sense if the top number is a user's actual score on a problem and the bottom number is the possible score, which is what it sounds like you are doing. This will enable you to sum up scores using the built in number functions in Oracle rather than parsing strings. Then, you limit the size of each column to (0-9) by using the type NUMBER(1,0). For example:

alter table table_name add (
    column possible number(1,0),
    column actual number(1,0)
);

If you have data in the score column already, you then copy your values over from there to your new columns. Finally, you drop that column.

alter table table_name drop score;

Also, I'd do a search on "Oracle less than constraint", because you probably don't want the actual score to exceed the possible, and probably do a similar constraint to make the possible score greater than zero.

I don't have an instance of Oracle to test against, but here are some pointers and some untested code:

Pointers:

• Look here on how to create a check constraint: http://www.techonthenet.com/oracle/check.php
• After you know that, you can use Regex to validate the input. Read here for further reference: http://psoug.org/reference/regexp.html

You are probably going to use REGEX_LIKE and it would look like something like this:

ALTER TABLE your_table
add CONSTRAINT check_your_field
   CHECK (REGEXP_LIKE(your_field, '^[0-9]+/[0-9]+$'));

Warning: this is not guaranteed to be fully functional code. It's a lead. Read, research and adjust accordingly.

A caveat: 1/0 will be considered valid by the Regex above, but we all know it should not be. There's a way you can know about the second part of the fraction. Read the Regex link, everything you need to know is there.

The domain of valid values is finite and small. Therefore, consider putting them all in a table, using a fixed width text column (i.e. three characters) and a create a foreign reference to this lookup table, rather than a CHECK constraint.