> 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@sigaev.ru
WWW: http://www.sigaev.ru/
