Oracle Check Constraint Issue with to_date

Question

So I'm new to Oracle, trying to create a table as follows:

create table Movies (
   Title   varchar2 primary key,
   Rating NUMBER CONSTRAINT Rating_CHK CHECK (Rating BETWEEN 0 AND 10),
   Length NUMBER CONSTRAINT Length_CHK CHECK (Length > 0),
   ReleaseDate DATE CONSTRAINT RDATE_CHK
               CHECK (ReleaseDate > to_date('1/1/1900', 'DD/Month/YYYY')),
   CONSTRAINT title_pk PRIMARY KEY (Title)
)

Per my assignment, the ReleaseDate must have a constraint enforcing only dates after 1/1/1900. The input my professor has given us for dates is as follows: 13 August 2010

Can one of you experts see where my issue lies?

Answer 1

The spec for Title column is incorrect, as well as the date string/format model combination in your to_date function call. Specify a column length for TITLE, and fix the date string to match the format model.

Try this:

create table Movies (
   Title   varchar2(100),
   Rating NUMBER CONSTRAINT Rating_CHK CHECK (Rating BETWEEN 0 AND 10),
   Length NUMBER CONSTRAINT Length_CHK CHECK (Length > 0),
   ReleaseDate date CONSTRAINT RDATE_CHK CHECK (ReleaseDate > to_date('1/January/1900', 'DD/Month/YYYY')),
   CONSTRAINT title_pk PRIMARY KEY (Title)
)

Update: As an aside, Title is a lousy primary key. Ever hear of two different movies with the same title? Can you say "remake"?

Another edit. I guess since your prof gave you the date format, you should make the date string match the format model. I've updated my answer.

Answer 2

I think 'Month' in TO_DATE is looking for a month name - not a number. Either change the second 1 to January or change Month to MM.