CODEWITHSUNDEEP
sqloracle-databaseoracle10goracle-sqldeveloper
Najib
Najib

Reputation: 229

Unique constraint on two columns regardless of order - Oracle

disclaimer: I have read the similar questions, it is different.

We have to create a flight table with 'DEPART' and 'ARRIVE' entries (that are 3 long chars (flight from LON to LAX) and distance, and other entries etc. The main constraint is, that the DEPART and ARRIVE pairs should be unique regardless of their order (direction does not matter). What I know I can do but we are not allowed to (so do not suggest):

So how can I check if a pair exist in the table or not? I gotta say it is not for an industrial task, it is for a university subject. We use Oracle SQLDeveloper for the task. Thank you in advance! Regards.

Upvotes: 1

Views: 376

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269793

Oracle supports indexes on expressions, so:

create index unq_t_from_to on t(least(from, to), greatest(from, to))

(Of course, from and to are keywords in SQL so they are not very good for column names.)

Upvotes: 4

Related Questions