Search Postgresql Archives

Re: deleting the master but not the detail

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux