on delete set null problem

[pu2t]

hi, i have home work to build a database. there is a rule stated if airplane removed from database, it won't removed flight schedule that use the airplane. i have made the DDL, but it's not work

create table airplane(
no_airplane serial primary key,
brandvarchar(20 ) not null,
model varchar(20) not null,
year_madechar(4 ) not null,
date_of_buy date default current_date);

create table flight_schedule (
kode_of_flight varchar(9) primary key,
no_airplane serial constraint fk_flight_sched ule
references airplane(no_air plane) on delete set null,
date_departure date,
time_departure time,
date_arrive date,
time_arrive time,
check (date_arrive>=d ate_departure and time_arrive>tim e_departure time));

when i delete one of airplane data, error message displayed. i need helps. n i'm sorry for my english.

[michaelb]

You cannot use serial for column no_airplane in flight_schedule .
(Even if you could set to to null, it still would be a bad idea to use serial in this particular case)
Try an integer instead.

You also seem to have a problem with your check conditions:

check (date_arrive>=date_departure and time_arrive>time_departure time));

time_arrive can be less than time_departure, it happens all the time when you take off by the end of the day.
Even date_arrive can be less than date_departure if you think about it :)

[pu2t]

ya, i just realize it

so, can u give me advices to solve it? in the task it stated that the date of arrival must not less than the date of departure. thanx

[michaelb]

I assume you don't have to worry about time zones, loval time, etc....
In this case the easiest approach for you could be to combine your two fileds (date and time) into one field; timestamp(0) may work well for you.
The timestamp field will have both - the date and the time.
The value on such field may look something like this "2006-01-03 20:30:45"
and you can implement your rule arrival > departure.