LinuxQuestions.org
View the Most Wanted LQ Wiki articles.
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - General
User Name
Password
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.

Notices

Reply
 
Search this Thread
Old 11-06-2004, 09:03 AM   #1
benr77
Member
 
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, 10:38 AM   #2
btmiller
Senior Member
 
Registered: May 2004
Location: In the DC 'burbs
Distribution: Arch, Scientific Linux, Debian, Ubuntu
Posts: 4,059

Rep: Reputation: 295Reputation: 295Reputation: 295
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, 10:48 AM   #3
benr77
Member
 
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.
 
  


Reply


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 05:49 AM
HOWTO access Processor ID / other unique values?? kartheekpn Linux - Security 5 03-20-2005 04:10 AM
MySQL Returning very strange Values in PHP Script benrose111488 Programming 2 10-21-2004 11:26 AM
how do I Add multiple columns mysql in postgres Bheki Linux - Software 1 08-28-2003 02:30 PM
storing multiple values within one field in mysql antken Programming 8 12-15-2002 10:08 PM


All times are GMT -5. The time now is 09:43 PM.

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