CODEWITHSUNDEEP
oracle-databaseoracle11g
Prasoon Nath
Prasoon Nath

Reputation: 1

Multi-column check constraints

I have a "movies" table and I need to add a constraint to the effect that "Steven Spielberg" movies must be before 1990 and "James Cameron" movies must be after 1990.

I am new to Oracle SQL. I tried some queries but they did not work. Something like 

alter table movie_16bce1346 add constraint chk_spk 
                if director='Steven Spielberg' then check (year>1990);

What's the right way to write this constraint?

Upvotes: 0

Views: 402

Answers (2)

bbrumm
bbrumm

Reputation: 1352

You can use a CASE statement within a CHECK constraint:

ALTER TABLE movie_16bce1346 
ADD CONSTRAINT check_year CHECK
  (CASE
    WHEN director = 'Steven Spielberg' AND movie_year > 1990 THEN 1 
    WHEN director = 'James Cameron' AND movie_year < 1990 THEN 1
    WHEN director NOT IN ('Steven Spielberg', 'James Cameron') THEN 1
    ELSE 0 END = 1);

The CASE captures your conditions, which need to return a 1 or 0. The "END = 1" says that the result of the CASE needs to equal 1.

Upvotes: 0

user5683823
user5683823

Reputation:

The constraint should be something like this:

( director not in ('Steven Spielberg', 'James Cameron') or
  director is null   /*  if that is allowed  */         or
  director = 'Steven Spielberg' and movie_year < 1990   or
  director = 'James Cameron'    and movie_year > 1990       )

You could use parentheses around the AND-separated conditions in lines 3 and 4, if you don't trust your developers to be very familiar and comfortable with the precedence of AND and OR.

Upvotes: 1

Related Questions