delete records on table

vickr1z · 11-18-2004, 12:36 AM

good day to all..
any body has good idea how can i delete a rows of record/s on a table with reference key to another table?? BTW im using Postgres..

To give you some idea here's my two table schema.

Code:

CREATE TABLE public.personalities
(
  personality_id serial NOT NULL,
  personality_nam_last public.names_last NOT NULL,
  personality_nam_first public.names_first NOT NULL,
  personality_nam_middle public.names_middle,
  personality_nam_suffix public.names_suffix,
  personality_nam_alias varchar(50),
  birth_date date NOT NULL,
  birth_prov_ipd_id public.areas_ids,
  birth_munc_ipd_id public.areas_ids,
  birth_brgy_ipd_id public.areas_ids,
  gender char(1) NOT NULL,
  db_user public.db_users NOT NULL DEFAULT "current_user"(),
  timestamp public.timestamps NOT NULL DEFAULT now(),
  CONSTRAINT personalities_pkey PRIMARY KEY (personality_id),
  CONSTRAINT "$1" FOREIGN KEY (birth_prov_ipd_id) REFERENCES public.provinces (province_code) ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT "$2" FOREIGN KEY (birth_munc_ipd_id) REFERENCES public.municipalities (municipality_code) ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT "$3" FOREIGN KEY (birth_brgy_ipd_id) REFERENCES public.barangays (bgy_code) ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT personalities_personality_nam_last_key UNIQUE (personality_nam_last, personality_nam_first, birth_date),
  CONSTRAINT personalities_gender CHECK ((gender = 'M'::bpchar) OR (gender = 'F'::bpchar))
) WITH OIDS;
REVOKE ALL ON TABLE public.personalities FROM public;
GRANT ALL ON TABLE public.personalities TO dbadmin;
GRANT SELECT, UPDATE, INSERT ON TABLE public.personalities TO GROUP encoders;
GRANT SELECT ON TABLE public.personalities TO GROUP "general users";

CREATE TABLE public.personalities_etc
(
  personality_etc_id serial NOT NULL,
  personality_id int4,
  civil_status char(1) NOT NULL,
  citizenship char(2),
  spouse_nam_last public.names_last,
  spouse_nam_first public.names_first,
  spouse_nam_middle public.names_middle,
  spouse_nam_suffix public.names_suffix,
  profession text NOT NULL,
  street_address varchar(50) NOT NULL,
  address_prov_ipd_id public.areas_ids,
  address_munc_ipd_id public.areas_ids,
  address_brgy_ipd_id public.areas_ids,
  db_user public.db_users NOT NULL DEFAULT "current_user"(),
  timestamp public.timestamps NOT NULL DEFAULT now(),
  CONSTRAINT personalities_etc_pkey PRIMARY KEY (personality_etc_id),
  CONSTRAINT "$1" FOREIGN KEY (personality_id) REFERENCES public.personalities (personality_id) ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT "$2" FOREIGN KEY (address_prov_ipd_id) REFERENCES public.provinces (province_code) ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT "$3" FOREIGN KEY (address_munc_ipd_id) REFERENCES public.municipalities (municipality_code) ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT "$4" FOREIGN KEY (address_brgy_ipd_id) REFERENCES public.barangays (bgy_code) ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT personalities_etc_citizenship CHECK ((citizenship = 'NB'::bpchar) OR (citizenship = 'NT'::bpchar)),
  CONSTRAINT personalities_etc_civil_status CHECK (((civil_status = 'S'::bpchar) OR (civil_status = 'M'::bpchar)) OR (civil_status = 'W'::bpchar))
) WITH OIDS;
REVOKE ALL ON TABLE public.personalities_etc FROM public;
GRANT ALL ON TABLE public.personalities_etc TO dbadmin;
GRANT SELECT, UPDATE, INSERT ON TABLE public.personalities_etc TO GROUP encoders;
GRANT SELECT ON TABLE public.personalities_etc TO GROUP "general users";
COMMENT ON TABLE public.personalities_etc IS 'Personalities additional information table.';

should i drop the constraints? how? any idea what should i do? thanks in advance..

rjlee · 11-25-2004, 06:27 AM

This depends on what you want to do with the referenced records, and is controlled by the ON DELETE clause, as described at http://scripts.postgresql.org/docs/7...CONSTRAINTS-FK

You can drop the constraints using the ALTER TABLE instruction, described at http://scripts.postgresql.org/docs/7...r.html#AEN2140