Teodor Sigaev wrote:
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));
Sorry, only by using triggers on insert/delete/update.
Aw, that's a shame... Well, I do have quite a bit of experience writing
triggers (been working on an avalanche of cascading triggers - works
wonderfully), so that's not really a problem.
It does make me wonder though, the foreign key reference was created ok,
but does it do anything this way? I suspect it does, this isn't MySQL
after all :P
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...
Is it really necessary to insert an 'empty' record for the root node?
The 'a' record from my experiments seems to be quite suited for the
task, unless I'm missing something.
alter table foo add foreign key subpath( path, 0, -1) references foo(
path )
deferrable initially deferred,;
IIRC, you can define equality for custom types depending on the
direction of the comparison. Isn't something like that possible for
foreign keys? You'd be able to check whether the left hand of the
comparison is a parent of the right hand and vice versa. That'd be just
what we need...
I must be missing something, you've obviously put a lot of thought in
ltree. Maybe it'll be possible with a future version of PostgreSQL :)
Regards,
--
Alban Hertroys
alban@xxxxxxxxxxxxxxxxx
magproductions b.v.
T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
7500 AK Enschede
// Integrate Your World //
