SQL Create Table Error

Question

Hello all. I am just beginning to learn SQL and have stumbled at the first hurdle, I am unable to create a table. Below is the code example. The error I am receiving when running the statement, references line 7 stating there is an issue with a 'relational operator'. The purpose of line 7 is to check that the age of the person is greater than 18.

I am using Oracle (unsure if that will make a difference). I hope someone can point me in the correct direction.

1.  CREATE TABLE employee
2.  (
3.  empID         VARCHAR2(20) NOT NULL primary key,
4.  surname       VARCHAR2(15) NOT NULL CHECK(surname=UPPER(surname)),
5.  deptCode      CHAR(5) NOT NULL CHECK(deptCode=UPPER(deptCode)),
6.  empYear       NUMBER(1,0) NOT NULL CHECK(empYear >= 1 AND empYear <= 4),
7.  birthDate     DATE NOT NULL CHECK((SYSDATE - birthDate) /365 ) >= 18
8.  );

Thank you.

Answer 1

Check your parenthesis.

DATE NOT NULL CHECK(((SYSDATE - birthDate) /365 ) >= 18)

EDIT: As Justin pointed out, you can't use SYSDATE here.

Answer 2

You cannot define a CHECK constraint that references a function like SYSDATE.

The Oracle documentation has a discussion on the restrictions on CHECK constraints