Problem with UPDATE and UNIQUE

Hi all

I have a problem, which I suspect stems from bad design.

If I explain what I am doing, perhaps someone can suggest a better approach.

I want to store data in a 'tree' form, with a fixed number of levels, so
that each level has a defined role.

I have the following (simplified) table -

CREATE TABLE treedata (
rowid serial primary key,
levelno int not null,
parentid int references treedata,
seq int not null,
code varchar not null,
description varchar not null
);

The 'root' item has a parentid of null, all other items must have a valid
parent. Items with a levelno of 0 represent raw data, higher levelno's
represent grouping levels. The seq indicator is used to display data in a
defined order.

To describe each of the levels in the tree, I have the following table -

CREATE TABLE treelevels (
levelno int primary key,
code varchar unique not null,
description varchar not null
);

Typical values for this table could be -
(0,'Prod','Product code')
(1,'Cat','Product category')
(2,'*','All products')

Now for the problem. I want to insert or delete levels dynamically. I can
insert or delete levels in 'treedata' without a problem. However, I also
want to insert or delete a level in 'treelevels'.

Say I want to insert a level between 'code' and 'category' called 'group' -

INSERT INTO treelevels VALUES (1,'Group','Product group');

Obviously this will fail with a duplicate levelno. Therefore before the
insert statement I want to do this -

UPDATE treelevels SET levelno = (levelno+1) WHERE levelno >= 1;

The problem is that if there are a number of levels, and they are in
indeterminate order, I can get duplicate level numbers while the command is
being executed.

My workaround at present is the following -

UPDATE treelevels SET levelno = (levelno+10001) WHERE levelno >= 1;
UPDATE treelevels SET levelno = (levelno-10000) WHERE levelno >= 1;

It works, but it feels very ugly.

Any suggestions will be much appreciated.

Thanks

Frank Millman