vickr1z |
11-17-2004 11:36 PM |
delete records on table
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..
|