Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL Oracle constraint value unique among multiple columns

Suppose I have 3 columns in a table - A, B and C. I want to ensure that if I insert some value (say x) into a column A, I can't insert a tuple that has either B or C equal to x, i.e, value x should remain unique to column A for all tuples.

Note that x can be repeated in A for some other tuple.

I'm aware of the UNIQUE clause in SQL, but that is only to ensure that a value occurs in a particular column only once. Since CHECK statements in Oracle do not allow subqueries, I can't figure out how to implement this.

EDIT (to add more info)

Primary key is Employee_Number, while the 3 columns in question are LandlineNo, MobileNo and VOIP. Thus suppose this was one entry :

Employee_Number = 1, LandlineNo = x, MobileNo = y, VOIP = z

Then this entry for another tuple would NOT be allowed -

Employee_Number = 2, LandlineNo = a, MobileNo = x, VOIP = c

On the other hand, this one would be fine (yes, 2 employees can have the same number of the same type)

Employee_Number = 2, LandlineNo = x, MobileNo = b, VOIP = c
like image 468
Noble Six Taniguchi Avatar asked Nov 02 '13 17:11

Noble Six Taniguchi


1 Answers

CREATE MATERIALIZED VIEW mv_my
BUILD IMMEDIATE
REFRESH FAST ON COMMIT AS
SELECT DISTINCT
    CASE 
        WHEN t2.Employee_Number IS NOT NULL THEN 1
        WHEN t3.Employee_Number IS NOT NULL THEN 1
        WHEN t4.Employee_Number IS NOT NULL THEN 1
        ELSE 0
    END AS wrong
FROM table t1
    LEFT JOIN table t2 ON t2.MobileNo = t1.LandlineNo AND t2.Employee_Number != t1.Employee_Number
    LEFT JOIN table t3 ON t3.VOIP = t1.LandlineNo AND t3.Employee_Number != t1.Employee_Number
    LEFT JOIN table t4 ON t4.VOIP = t1.MobileNo AND t4.Employee_Number != t1.Employee_Number
/

ALTER TABLE mv_my ADD CHECK(wrong = 0)
/

It may or may not work depending on your oracle version (doc)

like image 162
Sebas Avatar answered Sep 27 '22 18:09

Sebas