SQL Check Constraint - How to check for specific format?

Question

I am trying to add a CHECK constraint to my column for a football game score. The format has to be like such --> 4-2 (with the hyphen) Also both numbers cannot exceed 999.

Below is what I have so far which obviously does not work..Any ideas? Column data type is VARCHAR(7)

Constraint ScoreCheck CHECK(Score LIKE '0-999'-'0-999');

Please tag your question with the database you are using. Also, you should really be storing the score as two separate numeric columns, not as a single string column. Very silly to store numbers as strings, and to store two numbers in a single field. — Gordon Linoff, Jun 17 '15 at 12:22
Oracle. I agree that having 2 numbers in a field is silly, but this is the requirement was given - A column that records the scores of both teams in the match — Tester45, Jun 17 '15 at 12:28
That's not a system requirement, it's a method of implementation. You can always make two separate columns look like a single entity in an application. — David Aldridge, Jun 17 '15 at 12:38

score 2 · Answer 1 · answered Jun 17 '15 at 12:32

2

The correct way to do this is to use a view or virtual column. Virtual columns are available since 11g. Simply define the virtual column in the table, such as:

create table  . . . 
    WinningScore int,
    LosingScore int,
    GameScore as (WinningScore || '-' || LosingScore)
)

(You can also use alter table for an existing table.)

Here is a bit of a primer on virtual columns.

answered Jun 17 '15 at 12:32

Gordon Linoff

1,242,037
58
646
786

Why is this the *correct way*? I agree that is in many respects optimal and/or desirable (and I'd probably go this route), but correct, I am not so sure. – René Nyffenegger Jun 18 '15 at 04:53
@RenéNyffenegger . . . Because there are two separate attributes, the scores for each of the teams. Each attribute should be stored in its own column. Putting them together with a hyphen is an output format, and should not drive the data model. – Gordon Linoff Jun 18 '15 at 12:06

score 1 · Answer 2 · answered Jun 17 '15 at 12:33

Instead of LIKE you should use REGEXP_LIKE

Constraint ScoreCheck CHECK( REGEXP_LIKE(Score,'^[0-9]{1,3}-[0-9]{1,3}$');

Also for further use, you should split this field into home team score and away team score and store them as integer so processing them would be easy

SQL Check Constraint - How to check for specific format?

2 Answers2