By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,963 Members | 1,249 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,963 IT Pros & Developers. It's quick & easy.

on delete set null problem

P: 2
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_schedule
references airplane(no_airplane) on delete set null,
date_departure date,
time_departure time,
date_arrive date,
time_arrive time,
check (date_arrive>=date_departure and time_arrive>time_departure time));

when i delete one of airplane data, error message displayed. i need helps. n i'm sorry for my english.
Dec 25 '06 #1
Share this Question
Share on Google+
3 Replies

Expert 100+
P: 534
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:
Expand|Select|Wrap|Line Numbers
  1. 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 :)
Dec 26 '06 #2

P: 2
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
Jan 1 '07 #3

Expert 100+
P: 534
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.
Jan 2 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.