It avoids the compound keys but you still have that cascading data problem. , constraint neighbourhood_state_fk foreign key (state_id) , constraint neighbourhood_country_fk foreign key (country_id) , constraint city_country_fk foreign key (country_id) Your proposal is an alternate version of this: create table state Consequently, when the system's data changes - say there's a state boundary re-organisation - changes to a whole bunch of cities have to be cascaded to the Neighbourhood table, and any other children. Primary keys are not supposed to change, but compounding them creates meaning. This approach is deprecated because in the short term it creates exceedingly unwieldy joins and in the long term it creates horrible messes when the keys change. References city(country_id, state_id, city_id) , constraint neighbourhood_city_fk foreign key (country_id, state_id, city_id) , constraint neighbourhood_pk primary key (country_id, state_id, city_id, neighbourhood_id) , constraint city_state_fk foreign key (country_id, state_id) , constraint city_pk primary key (country_id, state_id, city_id) , constraint state_pk primary key (country_id, state_id) , constraint neighbourhood_city_fk foreign key (city_id)Īn alternative approach, which has largely fallen out of favour, is to define the primary keys of the child tables as compound keys including the keys of the immediate parent table: create table state , constraint neighbourhood_pk primary key (neighbourhood_id) , constraint city_state_fk foreign key (state_id) , constraint city_pk primary key (city_id) , constraint state_country_fk foreign key (country_id) , constraint state_pk primary key (state_id) , constraint country_pk primary key (country_id) The closest thing there is to an industry standard is this: each dependent table is linked by a foreign key to its immediate parent: create table country
0 kommentar(er)
