I have a table containing two attributes with year information (as numbers)
DateOfBirth Number(4) NULL,
DateDeceased Number(4) NULL,
I also made a check constraint to make sure the date is correct like this:
CONSTRAINT BirthValueCheck CHECK (DateOfBirth < DateDeceased),
CONSTRAINT ValidBirthYear CHECK (DateOfBirth LIKE '[1 - 2] [0 - 9] [0 - 9] [0 - 9]'),
CONSTRAINT ValidDeathYear CHECK (DateDeceased LIKE '[1 - 2] [0 - 9] [0 - 9] [0 - 9]')
When I insert a new value into the table like this:
INSERT INTO ARTIST(ArtistID, LastName, FirstName, Nationality, DateOfBirth, DateDeceased)
VALUES(SEQ_ARTISTID.NEXTVAL, 'Miro', 'Joan', 'Spanish', '1893', '1983');
I get an error saying:
Error starting at line : 104 in command -
INSERT INTO ARTIST(ArtistID, LastName, FirstName, Nationality, DateOfBirth, DateDeceased)
VALUES(SEQ_ARTISTID.NEXTVAL, 'Miro', 'Joan', 'Spanish', '1893', '1983')
Error report -
SQL Error: ORA-02290: CHECK-beperking (DBI279057.VALIDDEATHYEAR) is geschonden.
02290. 00000 - "check constraint (%s.%s) violated"
*Cause: The values being inserted do not satisfy the named check
*Action: do not insert values that violate the constraint.
I don't get why it gives me this error, since 1983 seems valid with the constraint given. Also it doesn't give me an error with the DateOfBirth check constraint, which is basically the same constraint and is declared before the ValidDeathYear constraint. Is this some kind of bug or am I missing something?
The LIKE operator does not support this "regular expression" syntax that you are using. LIKE only supports two types of wildcards: a single character _ or multiple characters %. It also does not support ranges or "minimum" length values.
Plus: you should not use LIKE on a number. LIKE is for strings (varchar, char)
If you want to constrain the range of years, you need to use something like this:
CONSTRAINT ValidBirthYear CHECK (DateOfBirth between 1900 and 2100)
As the column is defined as number you can't store non-numeric values in it anyway.