|
Hi Having a foreign key table with column role_supplementary_namesometimes 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
)
|
|
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) 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 '17, 05:25)
robert
Replies hidden
See that other FAQ...
(26 Jul '17, 10:18)
Volker Barth
Thanks, Volker
(27 Jul '17, 03:50)
robert
|
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..
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.