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
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)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With