3

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
2

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 at 16:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.