Re: 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