2

EDIT: I've made the post significantly shorter. I hope it's more digestible now.


Consider the following schema:

CREATE TABLE X (
      x TEXT PRIMARY KEY
);

CREATE TABLE Y (
      y TEXT PRIMARY KEY
    , x TEXT NOT NULL
    , FOREIGN KEY (x) REFERENCES X(x)
);

CREATE TABLE _DATA (
      data_id INTEGER PRIMARY KEY
    , y TEXT NOT NULL
    , FOREIGN KEY (y) REFERENCES Y(y)
);

CREATE VIEW mydata AS

  SELECT X.x, Y.y

    FROM X JOIN Y     USING (x)
           JOIN _DATA USING (y);

This schema enforces the following constraints on the rows of the mydata view:

  1. no two rows may have the same y-value but different x-values;
  2. the x-value is never empty;
  3. the y-value is never emtpy;

The following output listing would be consistent with this schema:

Listing 1

SELECT * FROM mydata;
 x | y
---+---
 a | h
 b | i
 b | j
 c | k
 d | l
 a | h

Now, suppose that instead of the three constraints above we had the following:

  1'. no two rows may have the same non-empty y-value but different x-values;

  2'. the x-value is never empty;

(Constraint 2' is the same as constraint 2; constraint 1' entails adding the qualifier "non-empty" to constraint 1.)

With these new constraints the following listing, where the missing y-values are NULL, would become valid:

Listing 2

SELECT * FROM mydata;
 x | y
---+---
 a | h
 b | i
 b |
 c | k
 d |
 a | h

But such a listing would not be possible with the schema shown above. Even if the NOT NULL constraint were removed from the definition of the _DATA.y column, the closest we would get is

Listing 3

SELECT * FROM mydata;
 x | y
---+---
 a | h
 b | i
 c | k
 a | h

...because there's no way to determine the appropriate value of x for those rows where y is NULL.

I'm looking for a normalized schema that would enforce the new set of constraints (and thus allow data like that shown in Listing 2).


My "non-solution"

My "non-solution" to this problem is a hard-to-maintain hack. I post it here for three reasons:

  1. to clarify the situation described in the question above;
  2. to provide table-initialization code that responders can use to test their proposals if they so wish (it's in fact the code I used to generate Listing 2);
  3. to give an example of the sort of hard-to-maintain hack that I'm trying to avoid.

This "non-solution" consists of defining some distinct values of y that can somehow be recognized as NULL, and redefining mydata to make use of this information. For example:

INSERT INTO X VALUES
    ('a')
  , ('b')
  , ('c')
  , ('d')
;

INSERT INTO Y VALUES
    ('h', 'a')
  , ('i', 'b')
  , ('NULL_b', 'b')
  , ('k', 'c')
  , ('NULL_d', 'd')
;

INSERT INTO _DATA VALUES
    (1, 'h')
  , (2, 'i')
  , (3, 'NULL_b')
  , (4, 'k')
  , (5, 'NULL_d')
  , (6, 'h')
;


CREATE TEMP VIEW mydata AS
  SELECT x,
         CASE WHEN y LIKE 'NULL_%'
              THEN NULL
              ELSE y
         END AS y
    FROM X JOIN     Y USING (x)
           JOIN _DATA USING (y);

Then

SELECT * FROM mydata;
 x | y 
---+---
 a | h
 b | i
 b | 
 c | k
 d | 
 a | h
(6 rows)

Ugly as sin, but aesthetic aside, it is also very hard to maintain.

  • 2
    The schema you can get away with depends on the NULL semantics of your target database. In SQL Server, a unique index can only have one NULL value. In Oracle, because all NULL are not equal to each other, a unique index on a column can have more than one NULL value. What is your target database? – Thomas Kejser Dec 9 '14 at 16:20
  • 2
    Additionally: Are you looking for an "academically correct" solution or one you would actually use in real life? – Thomas Kejser Dec 9 '14 at 16:22
  • @ThomasKejser: First off, apologies for not responding to your comments earlier. Regarding your first question, I'm working with both PostgreSQL and SQLite3. (As an aside, I find it mind-blowing that the semantics of NULL in SQL would be left up to the implementation.) Second, I'd be interested in both the "academically correct" and "real life" solutions to this problem. (BTW, note that I've just edited my post slightly, and, in particular, I fixed a serious typo in the last line.) – kjo Feb 5 '15 at 13:50
  • 1
    @kjo: They are not left up to implementation. But there are many places that many DBMS differ from the standard. Nulls and Unique constraints is one of them (where SQL-Server differs from the rest.) Other DBMS differ in various other details. – ypercubeᵀᴹ Feb 5 '15 at 13:53
  • 1
    @kjo You can post your solution as an answer instead of adding it as an edit to the question if it solves the problem. – LowlyDBA Feb 5 '15 at 19:12
1

Other options I see:

A) add column x to table _data. A few more changes will be required:

CREATE TABLE X (
      x TEXT PRIMARY KEY
);

CREATE TABLE Y (
      y TEXT PRIMARY KEY
    , x TEXT NOT NULL
    , FOREIGN KEY (x) REFERENCES X(x),
    , UNIQUE (x, y)                    -- required for the modified FK below
);

CREATE TABLE _DATA (
      data_id INTEGER PRIMARY KEY
    , x TEXT NOT NULL
    , y TEXT
    , FOREIGN KEY (x, y) REFERENCES Y(x, y)    -- modified FK
    , FOREIGN KEY (x) REFERENCES X(x)          -- additional FK
);

The view has to be modified as well:

CREATE VIEW mydata AS
  SELECT X.x, Y.y
    FROM X JOIN Y     USING (x)
           JOIN _DATA USING (y)
  UNION ALL
  SELECT X.x, NULL
    FROM X JOIN _DATA USING (x) 
  WHERE _DATA.y IS NULL ;

Example:

INSERT INTO X (x) VALUES
    ('a')
  , ('b')
  , ('c')
  , ('d')
;

INSERT INTO Y (y, x) VALUES
    ('h', 'a')
  , ('i', 'b')
  , ('k', 'c')
;

INSERT INTO _DATA (data_id, x, y) VALUES
    (1, 'a', 'h')
  , (2, 'b', 'i')
  , (3, 'b', NULL)
  , (4, 'c', 'k')
  , (5, 'd', NULL)
  , (6, 'a', 'h')
;

SELECT x, y FROM mydata;
 x | y 
---+---
 a | h
 b | i
 c | k
 a | h
 b | 
 d | 
(6 rows)

B) separate the data that have no y value associated to another table. No changes at all required to the existing tables, only for the view:

CREATE TABLE X (
      x TEXT PRIMARY KEY
);

CREATE TABLE Y (
      y TEXT PRIMARY KEY
    , x TEXT NOT NULL
    , FOREIGN KEY (x) REFERENCES X(x)
);

CREATE TABLE _DATA (
      data_id INTEGER PRIMARY KEY
    , y TEXT NOT NULL
    , FOREIGN KEY (y) REFERENCES Y(y)
);

CREATE TABLE _DATA_X (
      data_x_id INTEGER PRIMARY KEY
    , x TEXT NOT NULL
    , FOREIGN KEY (x) REFERENCES X(x)
);

The view modified:

CREATE VIEW mydata AS
  SELECT X.x, Y.y
    FROM X JOIN Y     USING (x)
           JOIN _DATA USING (y)
  UNION ALL
  SELECT X.x, NULL 
    FROM X JOIN _DATA_X USING (x) ;

Example:

INSERT INTO X (x) VALUES
    ('a')
  , ('b')
  , ('c')
  , ('d')
;

INSERT INTO Y (y, x) VALUES
    ('h', 'a')
  , ('i', 'b')
  , ('k', 'c')
;

INSERT INTO _DATA (data_id, y) VALUES
    (1, 'h')
  , (2, 'i')
  , (3, 'k')
  , (4, 'h')
;

INSERT INTO _DATA_X (data_x_id, x) VALUES
    (1, 'b')
  , (2, 'd')
;

SELECT x, y FROM mydata;
 x | y 
---+---
 a | h
 b | i
 c | k
 a | h
 b | 
 d | 
(6 rows)
  • Thanks! (I've submitted an edit of your answer that fixes a typo in the (A) alternative.) – kjo Feb 6 '15 at 18:13
  • 1
    @kjo Yes, thnx, I missed that (and the NULL in the view as well). Also added the B option as I had no time yesterday. – ypercubeᵀᴹ Feb 6 '15 at 18:22
  • I've added some examples to your post; I hope that's OK with you. I found your solutions really instructive, especially (A). I did not expect that the reference of _DATA to Y(x, y) would work if _DATA contained rows with NULL values for y. – kjo Feb 6 '15 at 18:41
  • 1
    If a row has null (in any one of the columns), then the foreign key is accepted. There is a variation specified with MATCH FULL that requires either all columns to be not null (and match the FK) or all columns to be null but it's rarely used (Postgres docs have some explanations.) – ypercubeᵀᴹ Feb 6 '15 at 18:43

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.