LinuxQuestions.org
Share your knowledge at the LQ Wiki.
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Newbie
User Name
Password
Linux - Newbie This Linux forum is for members that are new to Linux.
Just starting out and have a question? If it is not in the man pages or the how-to's this is the place!

Notices

Reply
 
LinkBack Search this Thread
Old 11-17-2004, 11:36 PM   #1
vickr1z
Member
 
Registered: Mar 2004
Location: manila, philippines
Distribution: RH 7.3
Posts: 63

Rep: Reputation: 15
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..
 
Old 11-25-2004, 05:27 AM   #2
rjlee
Senior Member
 
Registered: Jul 2004
Distribution: Ubuntu 7.04
Posts: 1,990

Rep: Reputation: 65
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
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to insert same text in one field for 10,000 records table edhan Linux - Newbie 6 06-13-2005 03:48 AM
add/delete record in mysql table on linux from a WinXp machine using visual c++ SyncMaster Linux - Enterprise 2 05-30-2005 12:53 AM
MySQL non-realtime table-by-table mirroring Passive Linux - Software 1 01-20-2004 12:11 PM
How to import MS ACCESS Table including OLE filed into the MySQL Table ? myunicom Linux - General 1 11-28-2003 11:30 AM
MX Records Terri Linux - Networking 2 01-21-2002 07:06 AM


All times are GMT -5. The time now is 07:48 PM.

Main Menu
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
identi.ca: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration