ORACLE constraints based on 2+ tables -

i have following 3 tables:

create table flights (   route_number int not null primary key,   operated_by int not null,   airplane_model  varchar(30) not null,   source varchar(20),   destination varchar(20),   foreign key (operated_by) references airline(airline_id),   foreign key (airplane_model) references airplanes(airplane_id)   ); create table outgoing (   route_number int not null primary key,   potime date not null,   foreign key (route_number) references flights(route_number) );      create table incoming (     route_number int not null primary key,     patime date not null,     foreign key (route_number) references flights(route_number)   );   create table departures(     depid int not null primary key,   route_number int not null,    gate varchar(3) not null,     dept date not null,     depd date not null, status varchar(10),     foreign key (route_number) references flights(route_number) );          create table arrivals(   route_number int not null,    gate varchar(3) not null,     arrt date not null,     arrd date not null, status varchar(10),     arrid int not null primary key,     foreign key (route_number) references flights(route_number) );    

i trying create constraint no 2 flights can have same gate during planned time (patime , potime) interval of -1,+1 hour around planned time.

i thought should create view , insert view...but apparently not allowed have subqueries in updateable view table? how go adding constraint?

note: patime , potime planned arrival time , planned departure times whereas arrt , dept actual arrival , actual departure times, , allowed different.

so basically: 1. flight added flight table (no constraints needed) 2. if it's incoming flight, planned arrival time added arrivals table (again no constraints worry about). 3. in arrivals table, it's actual arrival time (can different planned arrival time), status, , gate added table. need constraint! check if gate number exists in table , departures table -1 +1 hours planned arrival/departure times. table need way of joining incoming (for patime) outgoing (for potime) time , check constraints

a constraint can reference 1 table. if required use data model, cannot create constraint want.

you have few options

1. instead of creating constraint, can create stored procedure insert of new flight , implements whatever validations want. restrict application issuing direct insert on tables , require use stored procedure instead.
2. you can keep existing data model , create on commit materialized view combines data departures , arrivals , create constraint on materialized view.
3. you can modify data model information need constraint in single table.