Why does a Parent table not have a matching Primary or Unique key, when a foreign key is referenced to the parent table?

So I'm having this issue where my table's foreign key is not referencing the previous table correctly. I am coding in DB2 for IBM i (using the older language where COLLECTIONS is being used instead of SCHEMA----its part of the curriculum at my college)

the exact error message is:

SQL State: 42890 Vendor Code: -573 Message: [SQL0573] Table EMPLOYEE in UMALIK5 does not have a matching parent key. Cause . . . . . : A referencing column list was specified in the FOREIGN KEY clause for constraint CAMPUS_FK in UMALIK5. The parent table EMPLOYEE in UMALIK5 does not have a matching PRIMARY or UNIQUE key. If the file is partitioned, the access path of the parent file must be non-partitioned. The constraint cannot be added. Recovery . . . : Do one of the following and try the request again: -- Specify a table in the FOREIGN KEY clause that has a PRIMARY or UNIQUE key that matches the referencing column list. -- Change the referencing column list to match the definition of the PRIMARY or UNIQUE key defined on the parent table. -- If the parent file is partitioned, add a non-partitioned access path. Processing ended because the highlighted statement did not complete successfully

The error I looked up on IBM's support page and it was even more confusing than this.

Here is my code:

CREATE TABLE UMALIK5.POSITION (
POSNCODE VARCHAR (10) CONSTRAINT UMALIK5.POSITION_PK PRIMARY KEY,
POSNDESC VARCHAR (50));

SELECT * FROM UMALIK5.SYSTABLES;
SELECT * FROM UMALIK5.SYSCOLUMNS
 WHERE TABLE_NAME = 'EMPLOYEE';
CREATE TABLE UMALIK5.EMPLOYEE (
EMP_NUM VARCHAR (10) CONSTRAINT UMALIK5.EMPLOYEE_PK PRIMARY KEY,
EMP_F_NAME VARCHAR (50),
EMP_L_NAME VARCHAR (50),
MANAGER_NUM VARCHAR (10),
SUPERVISOR_CODE VARCHAR (10),
PAYRATE VARCHAR (20),
POSNCODE VARCHAR (10),
HIREDATE VARCHAR (50),
CONSTRAINT UMALIK5.EMPLOYEE_FK FOREIGN KEY (POSNCODE)
REFERENCES UMALIK5.POSITION(POSNCODE));

CREATE TABLE UMALIK5.CAMPUS (
CAMPUS_ID VARCHAR (10) CONSTRAINT UMALIK5.CAMPUS_PK PRIMARY KEY,
CAMPUS_NAME VARCHAR (30),
MANAGER_NUM VARCHAR (10),
CONSTRAINT UMALIK5.CAMPUS_FK FOREIGN KEY (MANAGER_NUM)
REFERENCES UMALIK5.EMPLOYEE(MANAGER_NUM));

I'm at a loss as to what I need to do, the table Employee creates just fine when I run my DB2 script, but for some reason the CAMPUS table does not.

What am I doing wrong?

thank you for your help.

Solution

Take the error message literally. In order to have a foreign key, you must reference something in the parent table that will return exactly one record. You are trying to reference manager_num which is not only not unique, but could actually be null. That's why db2 is barking at you.