"Missing right parenthesis" occurs for lots of different errors. In this case, the error has nothing at all to do with parentheses: the error is that you can't call SYSDATE in a CHECK constraint. Look up "Check Constraints" in the SQL Language manual:
for this and other restrictions.
You could hard-code a year in a check constraint, and then, every year, add a new constraint with a different year, and drop the old one.
Another work-around is to have a BEFORE INSERT OR UPDATE trigger, which raises an error (with a decent error message) if someone tries to enter a year that's too late.
Thanks for the info. What would be the exact syntax if I wanted to hardcode a date?
I tried the below, but still got that parenthesis error.
CONSTRAINT TEST_YEAR_CK CHECK (Year <= '2013');
Almost. If year is a NUMBER, then you should compare it to another NUMBER, not a VARCHAR2:
CONSTRAINT TEST_YEAR_2013_CK CHECK (Year <= 2013)
In other words, lose the single-quotes.
hmmmm. I tried that as well. Still get the same error (below):
SQL> CREATE TABLE TEST
2 (Name VARCHAR2(7) PRIMARY KEY,
3 Year NUMBER(4),
4 CONSTRAINT TEST_YEAR_CK CHECK (Year <= 2013);
CONSTRAINT TEST_YEAR_CK CHECK (Year <= 2013)
ERROR at line 4:
ORA-00907: missing right parenthesis
Now you actually are missing a right parenthesis. The left parenthesis at the beginning of line 2 doesn't have a matching right parenthesis.
It helps to indent your code, so that things that have to occur in pairs (like parentheses), if they're separated by a lot of code, occur one right above the other, with only white-space in between them, like this:
CREATE TABLE test
( Name VARCHAR2 (7) PRIMARY KEY,
Year NUMBER (4),
CONSTRAINT Test_Year_2013_CK CHECK (Year <= 2013)