We've been experimenting with a table containing a branch 'a', 'a.b' and
'a.b.c', but deleting 'a.b' didn't cause a constraint violation.
SQL> CREATE TABLE ltree_test (path ltree PRIMARY KEY REFERENCES
ltree_test(path));
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"ltree_test_pkey" for table "ltree_test"
CREATE TABLE
SQL> INSERT INTO ltree_test VALUES ('a'::ltree);
INSERT 84117368 1
SQL> INSERT INTO ltree_test VALUES ('a.b'::ltree);
INSERT 84117369 1
SQL> INSERT INTO ltree_test VALUES ('a.b.c'::ltree);
INSERT 84117370 1
SQL> DELETE FROM ltree_test WHERE path = 'a.b'::ltree;
DELETE 1
SQL> select * from ltree_test;
path
-------
a
a.b.c
(2 rows)
Is there some obvious/easy way to prevent this?
Sorry, only by using triggers on insert/delete/update.
If it was a possible to use function in foreign key then it might looks as
create table foo (
path ltree not null
);
insert into foo values (''); -- root of tree, but it unremovable...
create unique index path_foo_idx on foo ( path ); -- BTree index for constraint
alter table foo add foreign key subpath( path, 0, -1) references foo( path )
deferrable initially deferred,;
But it's impossible...
--
Teodor Sigaev E-mail: teodor@xxxxxxxxx
WWW: http://www.sigaev.ru/
