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

Note that there are some explanatory texts on larger screens.

plurals
  1. POWhy does a Parent table not have a matching Primary or Unique key, when a foreign key is referenced to the parent table?
    primarykey
    data
    text
    <p>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)</p> <p>the exact error message is:</p> <blockquote> <p>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</p> </blockquote> <p>The error I looked up on IBM's support page and it was even more confusing than this.</p> <p>Here is my code:</p> <pre><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)); </code></pre> <p>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.</p> <p>What am I doing wrong?</p> <p>thank you for your help.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USUmeed
    1. USUmeed
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POWhy does a Parent table not have a matching Primary or Unique key, when a foreign key is referenced to the parent table?
      1. This table or related slice is empty.
      1. VTUpMod
    1. CONot sure if it's the cause of your error, but a normalized database will reference the primary key of the parent table when setting up foreign keys. You are not doing that.
      singulars
      1. POWhy does a Parent table not have a matching Primary or Unique key, when a foreign key is referenced to the parent table?
      1. USDan Bracuk
    2. COIn my new cumulative design, i have this Campus [CampusID, CampusName, Mnger#] FK Mnger# --> Employee however in my Employee table I have posncode fk to position table....so that's what I tried to make here in the db2 code...Am I suppose to make a composite key in the employee table with emp_num and manager_num? or am I suppose to somehow make a composite foreign key in the campus table?
      singulars
      1. POWhy does a Parent table not have a matching Primary or Unique key, when a foreign key is referenced to the parent table?
      1. USUmeed
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload