[GENERAL] deleting the master but not the detail
hi I have one of those master-detail relationships here and I need to be able to delete the master but leave the details untouched But the delete command doesn't let me delete the master as long as there are details referencing it. ON DELETE RESTRICT | NO ACTION won't let me delete the master CASCADE | SET NULL | SET DEFAULT will modify the details Any idea ow to do it without the use of triggers? SQL Server is got the option ON DELETE DO NOTHING or something like that, that just oversees the deletion, but I didn't find it for postgres. _ P.D. Checa las nuevas fotos de mi Space http://home.services.spaces.live.com/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] deleting the master but not the detail
am Thu, dem 17.07.2008, um 11:11:00 -0500 mailte Ismael folgendes:
hi
I have one of those master-detail relationships here and I need to be able
to delete the master but leave the details untouched
But the delete command doesn't let me delete the master as long as
there are details referencing it.
ON DELETE RESTRICT | NO ACTION won't let me delete the master
CASCADE | SET NULL | SET DEFAULT will modify the details
Any idea ow to do it without the use of triggers?
DROP the constraint. For example:
test=# create table master (id serial primary key, m text);
NOTICE: CREATE TABLE will create implicit sequence master_id_seq for serial
column master.id
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index master_pkey
for table master
CREATE TABLE
test=*# insert into master (m) values ('master1');
INSERT 0 1
test=*# insert into master (m) values ('master2');
INSERT 0 1
test=*# create table slave (id int references master, s text);
CREATE TABLE
test=*# insert into slave values (1, 'slave 1');
INSERT 0 1
test=*# insert into slave values (2, 'slave 2');
INSERT 0 1
test=*# \d slave
Tabelle »public.slave«
Spalte | Typ | Attribute
+-+---
id | integer |
s | text|
Fremdschlüssel-Constraints:
»slave_id_fkey« FOREIGN KEY (id) REFERENCES master(id)
test=*# alter table slave drop constraint slave_id_fkey;
ALTER TABLE
test=*# drop table master;
DROP TABLE
test=*# select * from slave;
id |s
+-
1 | slave 1
2 | slave 2
(2 Zeilen)
test=*#
Hope that helps, Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: - Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] deleting the master but not the detail
On 17/07/2008 17:11, Ismael wrote: ON DELETE RESTRICT | NO ACTION won't let me delete the master CASCADE | SET NULL | SET DEFAULT will modify the details Can you just drop the constraint that's doing the referential integrity? Ray. -- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland [EMAIL PROTECTED] Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals -- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] deleting the master but not the detail
So is there no other way to do it but to verify the integrity using triggers and drop the referential constraints? Because I *still* need to verify that NEW records in the details table direct to something that exists ON DELETE RESTRICT | NO ACTION won't let me delete the master CASCADE | SET NULL | SET DEFAULT will modify the details Can you just drop the constraint that's doing the referential integrity? Ray. Then remove the referential integrity constraint, since it's obviously incompatible with your business requirements. -Doug _ PlugPlay te trae en exclusiva los mejores conciertos de la red http://club.prodigymsn.com/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] deleting the master but not the detail
On Thu, Jul 17, 2008 at 12:11 PM, Ismael [EMAIL PROTECTED] wrote: hi I have one of those master-detail relationships here and I need to be able to delete the master but leave the details untouched Then remove the referential integrity constraint, since it's obviously incompatible with your business requirements. -Doug -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] deleting the master but not the detail
On Thu, Jul 17, 2008 at 10:52 AM, Douglas McNaught [EMAIL PROTECTED] wrote: On Thu, Jul 17, 2008 at 12:11 PM, Ismael [EMAIL PROTECTED] wrote: hi I have one of those master-detail relationships here and I need to be able to delete the master but leave the details untouched Then remove the referential integrity constraint, since it's obviously incompatible with your business requirements. Generally I'd agree, but it might be useful to set the child record referencing id field to NULL in this circumstance. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] deleting the master but not the detail
It seems like 3 vs 1 so you win :) I'll drop the constraint and verify the integrity of the new records manually tanks On Thu, Jul 17, 2008 at 10:52 AM, Douglas McNaught wrote: On Thu, Jul 17, 2008 at 12:11 PM, Ismael wrote: hi I have one of those master-detail relationships here and I need to be able to delete the master but leave the details untouched Then remove the referential integrity constraint, since it's obviously incompatible with your business requirements. Generally I'd agree, but it might be useful to set the child record referencing id field to NULL in this circumstance. _ Tenemos lo que búscas…JUEGOS. http://club.prodigymsn.com/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] deleting the master but not the detail
hi I have one of those master-detail relationships here and I need to be able to delete the master but leave the details untouched when you create the table with an FK constraint, use the ON DELETE SET NULL option, or SET DEFAULT. And read the docs on CREATE TABLE: http://www.postgresql.org/docs/8.3/static/sql-createtable.html Then remove the referential integrity constraint, since it's obviously incompatible with your business requirements. Wrong. See above. Unless I misunderstand, then I apologize for the noise -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] deleting the master but not the detail
On Thu, 17 Jul 2008, Ismael wrote: So is there no other way to do it but to verify the integrity using triggers and drop the referential constraints? Well, you could do something using a before delete trigger on the referencing table that returns NULL to avoid the delete as well, but making it only prevent the deletions caused by the referential constraints might be difficult. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] deleting the master but not the detail
So is there no other way to do it but to verify the integrity using triggers and drop the referential constraints? Well, you could do something using a before delete trigger on the referencing table that returns NULL to avoid the delete as well, but making it only prevent the deletions caused by the referential constraints might be difficult. That's right, It's easier to verify the existence of the master before inserting something in the details than controlling the way something gets deleted. After all, is only a PERFORM * FROM WHERE NEW. IF NOT FOUND THEN RISE EXCEPTION 'that doesn't exists';... _ PlugPlay te trae en exclusiva los mejores conciertos de la red http://club.prodigymsn.com/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
