Latest LQ Deal: Linux Power User Bundle
Go Back > Forums > Linux Forums > Linux - General
User Name
Linux - General This Linux forum is for general Linux questions and discussion.
If it is Linux Related and doesn't seem to fit in any other forum then this is the place.


  Search this Thread
Old 11-06-2004, 10:03 AM   #1
Registered: Sep 2004
Location: London, UK
Distribution: Fedora Core 4
Posts: 59

Rep: Reputation: 15
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?
Old 11-06-2004, 11:38 AM   #2
Senior Member
Registered: May 2004
Location: In the DC 'burbs
Distribution: Arch, Scientific Linux, Debian, Ubuntu
Posts: 4,284

Rep: Reputation: 371Reputation: 371Reputation: 371Reputation: 371
Hmmm ... good question. The easiest way might be to do it in the application layer itself (i.e. the code that actually uses the database). Have it check any new value against values existing id rgt and lft for duplicates.
Old 11-06-2004, 11:48 AM   #3
Registered: Sep 2004
Location: London, UK
Distribution: Fedora Core 4
Posts: 59

Original Poster
Rep: Reputation: 15
Well, the reason I don't want to do it in the application layer is that I want to use this to protect against faults in the application layer.

I want to have the database table configured so that it rejects any attempts by the application layer to insert invalid data.

Also, performing this checking in the application layer would incur a performance hit too.


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
MySQL Location on standard RH9 install bytebrowser Linux - Software 2 04-10-2005 06:49 AM
HOWTO access Processor ID / other unique values?? kartheekpn Linux - Security 5 03-20-2005 05:10 AM
MySQL Returning very strange Values in PHP Script benrose111488 Programming 2 10-21-2004 12:26 PM
how do I Add multiple columns mysql in postgres Bheki Linux - Software 1 08-28-2003 03:30 PM
storing multiple values within one field in mysql antken Programming 8 12-15-2002 11:08 PM > Forums > Linux Forums > Linux - General

All times are GMT -5. The time now is 05:04 PM.

Main Menu
Write for LQ is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration