Thread: constraint/restrict
hi there, with two tables i want to make some constraint-restrictions create table address ( id serial, country_id int4, ....); and create table country (id serial, ...); to make sure that now country-row is deleted if there is still a country_id in address table. e.g. address: 1, 2, ... country: 2, ... now country wouldn't be allowed to be deleted. how to do that? thanks fo help olaf -- soli-con Engineering Zanger, Dipl.-Ing. (FH) Olaf Marc Zanger Lorrainestrasse 23, 3013 Bern / Switzerland fon:+41-31-332 9782, mob:+41-76-572 9782 mailto:olaf.zanger@soli-con.com, http://www.soli-con.com
From: "Olaf Marc Zanger" <olaf.zanger@soli-con.com> > hi there, > > with two tables i want to make some constraint-restrictions > > to make sure that now country-row is deleted if there is still a country_id > in address table. > > e.g. > > address: 1, 2, ... > country: 2, ... > > now country wouldn't be allowed to be deleted. > > how to do that? You want a foreign-key (only in version 7) - check the reference manual for CREATE TABLE - and look for the keyword REFERENCES Basically, it's like: create table foo (fooid serial unique, footxt text); create table bar (barid serial, barfoo int4 references foo (fooid), bartxt text); Then after a few inserts... delete from foo where fooid=1; ERROR: <unnamed> referential integrity violation - key in foo still referenced from bar - Richard Huxton
add an foriegn key on address(country_id), let country(id) be a primary key. Jie LIANG St. Bernard Software Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 jliang@ipinc.com www.stbernard.com www.ipinc.com On Wed, 14 Feb 2001, Olaf Marc Zanger wrote: > hi there, > > with two tables i want to make some constraint-restrictions > > create table address ( id serial, country_id int4, ....); > and > create table country (id serial, ...); > > to make sure that now country-row is deleted if there is still a country_id > in address table. > > e.g. > > address: 1, 2, ... > country: 2, ... > > now country wouldn't be allowed to be deleted. > > how to do that? > > thanks fo help > > olaf > -- > soli-con Engineering Zanger, Dipl.-Ing. (FH) Olaf Marc Zanger > Lorrainestrasse 23, 3013 Bern / Switzerland > fon:+41-31-332 9782, mob:+41-76-572 9782 > mailto:olaf.zanger@soli-con.com, http://www.soli-con.com >