foreign key constraint where one column in foreign key is sometimes NULL

[huber1]

Hi

Having a foreign key table with column

role_supplementary_name

sometimes NULL, I tried to apply following statement but get an Error: No primary key 'persons_role_precised_by_fk' in table 'persons_roles'.

This seems to point to the fact that one of the foreign key column contains NULL values. I tried with the MATCH SIMPLE clause but with no success. Any hint is welcome.

ALTER TABLE persons_roles 
    ADD CONSTRAINT persons_role_precised_by_fk FOREIGN KEY 
    (
        role_supplementary_name, 
        role_id 
    ) 
    REFERENCES role_supplementaries 
    ( 
        name, 
        role_id
    )

[VolkerBarth]

Is the FK definition possibly declared as NOT NULL itself, possibly because the columns were NOT NULLable originally?

I'm relating to the fact that the FK definion has an optional NOT NULL clause, and I remember situations where I changed a NOT NULLable child column to NULLable and still could not insert nulls, until I also dropped and re-created the FK itself because that had (automatically) been declared as NOT NULL.

A wild guess..

[huber1]

One of column (role_id) is part of the primary key, the other one role_supplementary_name is "just" a foreign key column. This situation comes from overlapping attributes (two attributes from different relationships having the same content).

Please see details in the answer to Breck.

[Breck_Carter]

Check your data. It may be the non-null data causing the problem.

The behavior you want (now called MATCH SIMPLE) has been the default since at least Version 6.

CREATE TABLE role_supplementaries ( 
    name INT NOT NULL, 
    role_id INT NOT NULL, 
    str VARCHAR(10),
    PRIMARY KEY (name,role_id));

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

CREATE TABLE persons_roles ( 
    fpk INT PRIMARY KEY, 
    role_supplementary_name INT,
    role_id INT );

INSERT INTO persons_roles VALUES(100,1,10);   
INSERT INTO persons_roles VALUES(200,null,null);  
INSERT INTO persons_roles VALUES(300,2,null);  
INSERT INTO persons_roles VALUES(400,null,10);  
INSERT INTO persons_roles VALUES(500,null,11111);  
INSERT INTO persons_roles VALUES(600,22222,null);  

ALTER TABLE persons_roles 
    ADD CONSTRAINT persons_role_precised_by_fk FOREIGN KEY 
    (
        role_supplementary_name, 
        role_id 
    ) 
    REFERENCES role_supplementaries 
    ( 
        name, 
        role_id
    );

SELECT *, @@VERSION FROM persons_roles ORDER BY fpk;

        fpk role_supplementary_name     role_id @@VERSION
----------- ----------------------- ----------- ----------
        100                       1          10 6.0.4.3594
        200                  (NULL)      (NULL) 6.0.4.3594
        300                       2      (NULL) 6.0.4.3594
        400                  (NULL)          10 6.0.4.3594
        500                  (NULL)       11111 6.0.4.3594
        600                   22222      (NULL) 6.0.4.3594

        fpk role_supplementary_name     role_id @@VERSION
----------- ----------------------- ----------- -----------
        100                       1          10 17.0.7.3399
        200                  (NULL)      (NULL) 17.0.7.3399
        300                       2      (NULL) 17.0.7.3399
        400                  (NULL)          10 17.0.7.3399
        500                  (NULL)       11111 17.0.7.3399
        600                   22222      (NULL) 17.0.7.3399

Mismatched non-null values do cause a problem...

INSERT INTO persons_roles VALUES(700,22222,11111);

V6...
Could not execute statement.
No primary key value for foreign key 'persons_role_precised_by_fk' in
table 'persons_roles'
SQLCODE=-194, ODBC 3 State="23000"
Line 1, column 1
INSERT INTO persons_roles VALUES(700,22222,11111)

V17...
Could not execute statement.
No primary key value for foreign key 'persons_role_precised_by_fk' in
table 'persons_roles'
SQLCODE=-194, ODBC 3 State="23000"
Line 1, column 1
INSERT INTO persons_roles VALUES(700,22222,11111)

[huber1]

You are absolutely correct. The problem laid in the data, I had 4 rows having an empty string instead of NULL. That caused the problem.

After changing these to NULL, the constraint is applicable.

I am also tested the constraint with MATCH FULL (at the end of the statement) instead of using MATCH SIMPLE (the default as you mention). What I am surprised that the constraint is also applicable with MATCH FULL.

I understand the documentation the way that MATCH FULL means no one column (of my two columns) must contain a NULL value, but it seems I am reading it not correctly.

What exactly is then the difference between MATCH SIMPLE and MATCH FULL?

Thanks and regards, Robert

[VolkerBarth]

See that other FAQ...

[huber1]

Thanks, Volker