CODEWITHSUNDEEP
sqloracle-databasevarcharcheck-constraints
Tester45
Tester45

Reputation: 1

SQL Check Constraint - How to check for specific format?

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');

Upvotes: 0

Views: 3086

Answers (2)

Dhruv Raj Singh Rathore
Dhruv Raj Singh Rathore

Reputation: 79

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

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271151

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.

Upvotes: 2

Related Questions