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
    )

asked 25 Jul, 10:42

robert's gravatar image

robert
593303950
accept rate: 0%

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

(25 Jul, 17:36) Volker Barth

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.

(26 Jul, 05:19) robert
Comment Text Removed

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)
permanent link

answered 25 Jul, 13:37

Breck%20Carter's gravatar image

Breck Carter
26.9k440613886
accept rate: 21%

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

(26 Jul, 05:25) robert
Replies hidden

See that other FAQ...

(26 Jul, 10:18) Volker Barth

Thanks, Volker

(27 Jul, 03:50) robert
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×10

question asked: 25 Jul, 10:42

question was seen: 135 times

last updated: 27 Jul, 03:50