Tweeted twitter.com/StackDBAs/status/1141903923202199552
3 deleted 1 character in body
source | link

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

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

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 added 458 characters in body
source | link

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

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?

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 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
1
source | link

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?