0

Description

We have a table and data below :

Version: PostgreSQL 9.3
Table name: tree_data (id int, code text, name text, parent_id int) 
Primary key: id 
Foreign key: parent_id (refer to id)

and data:

insert into tree_data (id, code, name, parent_id) values (1, 'aaa','aaa', null);
insert into tree_data (id, code, name, parent_id) values (2, 'bbb','bbb', 1);
insert into tree_data (id, code, name, parent_id) values (3, 'ccc','ccc', 1); 

 id | code | name | parent_id
 1    aaa    aaa      null
 2    bbb    bbb      1
 3    ccc    ccc      1

Here our query and result we want, it means: when deleting id = 1 (parent row), table will automatically set parent_id = null in child rows (first level) .

delete from tree_data where id = 1 ;
----> rows after deleting: 
 id | code | name | parent_id
 2    bbb    bbb      null
 3    ccc    ccc      null

Our questions:

Can we use postgresql constraints to do it ? If not, how we can do ?

4
create table tree_data 
(
   id integer primary key, 
   code text, 
   name text, 
   parent_id integer,
   constraint fk_parent 
      foreign key (parent_id) 
      references tree_data(id)
      on delete set null
);

SQLFiddle example: http://sqlfiddle.com/#!15/b9a62/1

3

When you create a foreign key in postgres, set ON DELETE SET NULL.

ALTER TABLE public.tree_data
ADD CONSTRAINT tree_data_id_fkey
   FOREIGN KEY (parent_id)
   REFERENCES tree_data(id)
   ON DELETE SET NULL;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.