Enforcing a “disjoint or identical” constraint

check-constraintsconstraintoracle

Suppose that F is an Oracle table with two columns, X and Y, such that the constraint

CONSTRAINT unique_xy_pairs UNIQUE (X, Y)

is satisfied.

For each value [x] of F.X, let S[x] represent the set consisting of the values returned by the following (pseudo-SQL) query:

SELECT Y FROM F WHERE X = [x];

Now, I want to enforce the constraint that for any two arbitrary values [x1] and [x2] of F.X, their corresponding sets S[x1] and S[x2] must be either disjoint or identical.

How can I do this?

EDIT: A uniqueness constraint on F.Y would not be a valid solution, since such a constraint could be violated by tables that nonetheless satisfy the constraint described in the original statement.

For example, the following is an F table that does not satisfy a unique F.Y constraint, but satisfies the "disjoint or identical" constraint in the original post:

 X  Y
10  0
10  1
20  2
20  3
20  4
30  0
30  1

Best Answer

After I posted my question, I thought of a possible solution.

For concreteness, let's say that this is the table definition for F:

CREATE TABLE F (
    X NUMBER(10) NOT NULL,
    Y NUMBER(10) NOT NULL,
    CONSTRAINT xy_unique UNIQUE (X, Y)
);

We could replace this F with tables G, H, and I, defined as follows

CREATE TABLE I (
    ID NUMBER(10) PRIMARY KEY
);

CREATE TABLE G (
    X NUMBER(10) NOT NULL,
    YSET_ID NUMBER(10) NOT NULL,
    CONSTRAINT fk_g_yset_id FOREIGN KEY (YSET_ID) REFERENCES I(ID),
    CONSTRAINT x_unique UNIQUE (X)
);

CREATE TABLE H (
    YSET_ID NUMBER(10) NOT NULL,
    Y NUMBER(10) NOT NULL,
    CONSTRAINT fk_h_yset_id FOREIGN KEY (yset_id) REFERENCES I(ID),
    CONSTRAINT unique_y UNIQUE (Y)
);

The idea is that H is now a table of "y-sets". Each y-set consists of all the [y]'s corresponding to a given value of H.YSET_ID. The fundamental difference between F and H is that for H we can define a uniqueness constraint on H.Y, which ensures that these y-sets are disjoint.

Table I's only function is to relate G and H. (It would be convenient if G.YSET_ID could refer directly to H.YSET_ID, rather than indirectly via I.ID, but my understanding is that a foreign key must always refer to a primary key...)

(I don't know much about Oracle's version of SQL, so this solution is bound to be clumsy, or even syntactically incorrect.)

For example, if F is

 X  Y
10  0
10  1
20  2
20  3
20  4
30  0
30  1

...then the corresponding tables G, H, and I could be

G:
X  YSET_ID
10 100
20 101
30 100

H:
YSET_ID Y
100     0
100     1
101     2
101     3
101     4

I:
ID
100
101

As pointed out by @ypercubeᵀᴹ, the original F can be recovered from the new G, H, and I tables with

SELECT G.X, H.Y FROM G JOIN H ON G.YSET_ID = H.YSET_ID;

EDIT: Yet another afterthought: it turns out the there is a surprisingly symmetrical variation of the solution above:

CREATE TABLE I (
    ID NUMBER(10) PRIMARY KEY
);

CREATE TABLE G (
    X NUMBER(10) PRIMARY KEY,
    YSET_ID NUMBER(10) NOT NULL,
    CONSTRAINT fk_g_yset_id FOREIGN KEY (YSET_ID) REFERENCES I(ID)
);

CREATE TABLE H (
    Y NUMBER(10) PRIMARY KEY,
    YSET_ID NUMBER(10) NOT NULL,
    CONSTRAINT fk_h_yset_id FOREIGN KEY (yset_id) REFERENCES I(ID)
);

Even though it is not clear from my problem description, a table satisfying the desired constraint expresses a bijective function whose domain and codomain are both sets of disjoint sets. My second solution above makes this symmetry manifest.

Related Question