constraint in postgres
hi there, i want to make some constraint-restrictions with two tables in postgres create table address ( id serial, country_id int4, ....); and create table country (id serial, ...); i want to make sure that the country-row with "id=2" is not deleted if there is still a corresponding data-set with "country_id=2" in the 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:info@soli-con.com mailto:olaf.zanger@soli-con.com http://www.soli-con.com
Isn't this a classic "referential integrity" case? If so, you could try this: create table country (id serial primary key, ...); create table address (id serial, country_id int4 references country, ...); On Wed, Feb 14, 2001 at 09:29:27AM +0100, Olaf Zanger wrote:
i want to make some constraint-restrictions with two tables in postgres
create table address ( id serial, country_id int4, ....); and create table country (id serial, ...);
i want to make sure that the country-row with "id=2" is not deleted if there is still a corresponding data-set with "country_id=2" in the address table.
-- Fred Yankowski fred@OntoSys.com tel: +1.630.879.1312 Principal Consultant www.OntoSys.com fax: +1.630.879.1370 OntoSys, Inc 38W242 Deerpath Rd, Batavia, IL 60510, USA
+-------[ Fred Yankowski ]---------------------- | Isn't this a classic "referential integrity" case? If so, you could | try this: | | create table country (id serial primary key, ...); | | create table address (id serial, | country_id int4 references country, ...); references country(id) more than likely, unless you have a unique country_id in country. That is you don't need two fields with basicaly the same info, when you already have a unique serial id which is the primary key. This also stops you from inserting into address without a valid country, as a bonus side effect. -- Totally Holistic Enterprises Internet| P:+61 7 3870 0066 | Andrew Milton The Internet (Aust) Pty Ltd | F:+61 7 3870 4477 | ACN: 082 081 472 ABN: 83 082 081 472 | M:+61 416 022 411 | Carpe Daemon PO Box 837 Indooroopilly QLD 4068 |akm@theinternet.com.au|
participants (3)
-
Andrew Kenneth Milton -
Fred Yankowski -
Olaf Zanger