SAP SQL Anywhere

Question

The MATCH clause of a CREATE TABLE FOREIGN KEY clause allows to specify the handling of multi-column foreign keys.

In case I have a FK with two columns (say, ref1 and ref2) and the first one is NOT NULL but the second one may be NULL, I can declare the FK with MATCH SIMPLE to allow for that. (In contrast, MATCH FULL would prevent that.)

I can also add UNIQUE to the FK specification to allow UNIQUE values. However, the docs do only state that for NOT-NULL values:

If the UNIQUE keyword is specified, the referencing table can have only one match for non-NULL key values.

In my tests it seems that I can add several combinations of unique ref1 values when ref2 is null, i.e. the following sample (it's based on a sample from the cited doc page).

Two questions:

Is that expected behaviour for rows with NULL values?
If I want a different behaviour, namely to have the combination of both ref1 and ref2 to be unique and also to allow only one row with ref2 set to NULL per ref1, is there a way to specify this via a FK clause?
(I don't think so - it seems I have to add a separate UNIQUE index WITH NULLS NOT DISTINCT.)

CREATE TABLE pt2( 
    pk1 INT NOT NULL, 
    pk2 INT NOT NULL, 
    str VARCHAR(10),
    PRIMARY KEY (pk1,pk2));

INSERT INTO pt2 VALUES(1,10,'one-ten'), (1,30,'one-thirty'), (2,20,'two-twenty');
COMMIT;

CREATE TABLE ft3( 
    fpk INT PRIMARY KEY DEFAULT AUTOINCREMENT, 
    ref1 INT NOT NULL,
    ref2 INT NULL,
    FOREIGN KEY (ref1,ref2) 
       REFERENCES pt2 (pk1,pk2) MATCH UNIQUE SIMPLE);

INSERT INTO ft3 (ref1, ref2) VALUES (1,10);   -- succeeds
INSERT INTO ft3 (ref1, ref2) VALUES (1,10);   -- fails because not unique   
INSERT INTO ft3 (ref1, ref2) VALUES (2,20);   -- succeeds   
INSERT INTO ft3 (ref1, ref2) VALUES (1,20);   -- fails because there's no parent row with that combination
INSERT INTO ft3 (ref1, ref2) VALUES (1,30);   -- succeeds because of unique combination
INSERT INTO ft3 (ref1, ref2) VALUES (1,null); -- succeeds   
INSERT INTO ft3 (ref1, ref2) VALUES (1,null); -- succeeds again - now containing several rows with the combination "1/null"

SELECT * FROM ft3;

Accepted Answer

I see you have corrected our DCX examples already. Good catch there!

I believe your answers lies within the meaning of orphan rows in the definition of match when it comes to Referential Integrity. That article spells this out as an either/or definion:

  `. . . you can determine what constitutes`
  ` an orphaned row `
      ***versus*** 
  ` what constitutes a violation of referential integrity ....`

For Simple Match the fact that you do not match the entire key defines this to be an orphan row and so is neither restricted nor even covered by this Referencing or Foreign Key clause. This would also be true for Full Match for the case where all referencing columns have null values. {FWIW While not directly related to your questions, you control the nullability in the referencing column definitions and, thus, can control the ability to prevent orphans using that.}

From my tests (and your results) the Unique match is only effective on the whole keys. The phrase in DCX

 `"If the UNIQUE keyword is specified, the referencing table can have only 
   one match for non-NULL key values. " `

only applies for the case where none of the refrencing columns are null; ie. not an orphan. It is easy to misinterpret that in the English original ... and null logic is a bit confounding.

So for your first question, I do expect this to be correct behaviour. For your 2 rows with referencing values (1,null) neither match exactly either of the two 'parent' rows with (1,x) for the primary key values, but that does not matter since those are orphans (parentage undetermined/yet to be determine/do not care to know who their daddy is/ .... zenning Codd here a bit here, sorry) and, as orphans, they don't match anything.

For #2 you would need to add another layer. A unique table constraint would almost do, but that does not permit the null values you desire, so that just leaves a Unique Index to permit nulls; I guess. I would avoid check constraint logic myself for this.

HTH P.S. Of course you will need to work with UNIQUE...WITH NULLS NOT DISTINCT to get what you are looking for.

SAP SQL Anywhere

How does a FOREIGN KEY with MATCH UNIQUE SIMPLE treat null values?

Follow this question

Related questions