3

I have searched for solution to my problem and this question describes it perfectly.

Let´s say I have one table called ProjectTimeSpan (which I haven´t, just as example!) containing the columns StartDate and EndDate.

And that I have another table called SubProjectTimeSpan, also containing columns called StartDate and EndDate, where I would like to set a Check constraint that makes it impossible to set StartDate and EndDate to values "outside" the ProjectTimeSpan.StartDate to ProjectTimeSpan.EndDate

Kind of a Check constraint that knows about another tables values... Is this possible?

But I have a hard time to implement the solution to oracle. I've got even more puzzled when other articles stated that check constraint can not relate to other tables.

Community
  • 1
  • 1
VilPil
  • 35
  • 1
  • 3
  • check constraint can be implemented by function see: https://stackoverflow.com/a/3880853/1867646 (sure the function complexity affects the performance). – Marek-A- Jun 12 '23 at 18:35

1 Answers1

2

No it can't.

A FOREIGN KEY constraint can (and must) relate to another table, but it can only perform equiality checks.

I.e. you can test that a column (or a set of columns) are equal to those in the other table but not more complex conditions (like inside a span or whatever).

You'll have to implement a trigger for that.

Quassnoi
  • 413,100
  • 91
  • 616
  • 614