How to properly SET NULL on a delete with two attributes referenced?
От | Leon Starr |
---|---|
Тема | How to properly SET NULL on a delete with two attributes referenced? |
Дата | |
Msg-id | 281539F2-286D-4037-8466-6D5176D6102C@modelint.com обсуждение исходный текст |
Список | pgsql-novice |
Hello experts! I'm having some trouble with the following two tables that a tree of nodes. The rules I want to enforce are these: R1) Each node belongs to a tree R2) A node may or may not have a single parent node. R3) The parent of a node must belong to the same tree as the child. R4) A node may not have itself as a parent. My first attempt fails, and I wasn't too surprised... create table tree ( id int, primary key( id ) ); create table node ( id int, tree int, parent int, -- may be null, R2 primary key( id, tree ), -- R1 foreign key( parent, tree ) references node( id, tree ) on update cascade, -- R3, ** obvious problem here constraint parent_cycle check( parent != id ) -- R4 ); The thing is that I still want a cascade if the id of a parent node is updated. But I can see that I need to nullify the parent attribute if the parent is deleted, but I don't want to nullify the entire foreign key (parent, tree). Just the parent. I did try this: foreign key( parent, tree ) references node( id, tree ) on delete set null, But, unfortunately, the tree component of the foreign key is indiscriminately nulled. As always, help greatly appreciated! - Leon
В списке pgsql-novice по дате отправления: