Yes BUT you should never have a changeable value like street_name as the
primary / foreign key.
Instead you should have a street_name_lu table which has street_name_id
and street_name, with street_name_id as the PK. Then all tables that FK
into that will use the street_name_id which does not change, and the
street_name can be changed as needed, with all referencing tables "just
getting" the change.
Most particularly, to use the whole cascading delete / update mechanism,
or triggers for that matter, to deal with changes to primary key values,
is really a shame when it's so simple to solve by a better key design
strategy.
Mike Toews wrote:
Nenad Milasinovic wrote:
Hello,
I have some questions relating to PostGIS and PostgreSQL.
I am interested how GEO-data from PostGIS are connected to data in
PostgreSQL.
Say that we have vector layer of all city streets in PostGIS table,
with some street attributes (e.g. street name).
Say also that we have all data about locations in PostgreSQL table.
That data contains information about location street also.
What i want to accomplish is that when user change street name is
PostGIS table, that street name should automatically be changed in
all locations which belongs to that street. I am interested is there
any connection between tables in PostGIS and PostgreSQL
that could automatize this process, or i need to update all locations
with new street data by myself.
Thanks.
Hi Nenad,
PostGIS and PostgreSQL tables are pretty much the same. A "GIS" table
is one that has 1 or more column with type "geometry" and metadata in
the geometry_columns table.
It was mentioned previously, but to update references to a street name
in other tables you should investigate into a unique and foreign key.
So, for example, if you reference unique names of a street in one
table, give it a unique constraint:
ALTER TABLE street
ADD CONSTRAINT street_name_unique UNIQUE(street_name);
This means, however you can only have 1 "Broadway Street" in your
vacinity. If you have more than 1 distinctly named streets per city of
a metropolitan area, you may need to make a unique constraint using
two columns where it is unique, e.g.:
ALTER TABLE street
ADD CONSTRAINT city_street_name_unique UNIQUE(city, street_name);
Then the magic happens when you reference "Broadway Street" in another
table zero or more times using a foreign key with rules for "ON
UPDATE" and "ON DELETE" events to the unique reference. So, for
example, if you want the references to update themselves when the name
is updated, you need "ON UPDATE CASCADE" and when you want the
referenced rows to be deleted when the row with the unique reference
is deleted, you need "ON DELETE CASCADE". There are many options,
depending on what your are capturing and what you want to archive.
ALTER TABLE street_attributes
ADD CONSTRAINT street_attributes_street_name_fkey FOREIGN KEY
(street_name)
REFERENCES street (street_name) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE CASCADE;
-- or for (city, street_name) --
ALTER TABLE street_attributes
ADD CONSTRAINT street_attributes_city_street_name_fkey FOREIGN KEY
(city, street_name)
REFERENCES street (city, street_name) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE CASCADE;
If you have pgAdmin III you can create and explore the use and
behaviour of foreign key configurations using GUI dialogs, which is a
great way to learn.
-Mike
_______________________________________________
postgis-users mailing list
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
--
Regards, (please note new mobile number below)
Chris Hermansen mailto:[email protected]
tel+1.604.714.2878 · fax+1.604.733.0631 · mob+1.778.840.4625
Timberline Natural Resource Group · http://www.timberline.ca
401 · 958 West 8th Avenue · Vancouver BC · Canada · V5Z 1E5
_______________________________________________
postgis-users mailing list
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
