# Enforcing a “disjoint or identical” constraint

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
``````

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.

• As well written as this is it feels like a very 'theoretical' question and answer. It might be more useful to other readers if you could outline a real world problem or modelling issue that is resolves. – BriteSponge Jun 19 '19 at 16:08