MySQL - UNIQUE values across two columns - non-standard constraint issue
I am using the Modified Preorder Tree Traversal method to store heirarchical data in a MySQL database. This means that each node in the tree has a left and a right value. Now the left and the right values are unique to each node.
I want to add a unique key constraint to the 'rgt' and 'lft' columns, so that MySQL will not allow duplicate values. BUT, the values cannot be duplicated in the other column as well.
If I do separate unique indexes on both columns, then each column cannot contain duplicate entries, but the 'rgt' column could still contain a value that is also present somewhere in the 'lft' column etc.
If I do a unique index across both columns, then the combination of the two columns cannot be duplicated, but values in individual columns can be duplicated.
But what I need is a constraint that says if a certain value appears in one of the columns, then it a) cannot be duplicated in that same column, and b) cannot be duplicated in the other column either.
Does anyone know how I can achieve this?