foreign keys and lots of tables
От | Gary Stainburn |
---|---|
Тема | foreign keys and lots of tables |
Дата | |
Msg-id | 201105182010.19753.gary.stainburn@ringways.co.uk обсуждение исходный текст |
Ответы |
Re: foreign keys and lots of tables
|
Список | pgsql-sql |
I have the following tables (individual seat allocation removed to make it simpler) create table coaches ( -- carriages c_id serial primary key, c_name varchar(20) not null ); create table trains ( -- one for each train t_id serial primary key ); create table train_coaches ( -- which carriages are on what trains t_id int4 not null references trains(t_id), c_id int4 not null references coaches(c_id) ); I now want to create bookings and allocate seats, but the seat must exist on the coach_seats table *AND* only for a carriage included in the train, i.e. an entry in train_coaches. create table bookings ( b_id serial primary key, t_id int4 not null references trains(t_id) ); create table booking_seats ( b_id int4 not null references bookings(b_id), c_id int4, -- carriage ID c_seat varchar(10) -- seat label ); The following ensures the seat exists on the coach. (not shown) alter table booking_seats add constraint seat_exists foreign key (c_id, c_seat) references coach_seats (c_id,c_seat); How would I ensure that the coach exists on the train. I would need to convert the b_id to a t_id using the bookings table and I don't know how. To complicate things, when the initial booking is made, bot c_id and c_seat are NULL. Will this make any difference? Gary -- Gary Stainburn Group I.T. Manager Ringways Garages http://www.ringways.co.uk
В списке pgsql-sql по дате отправления: