https://communities.sas.com/t5/SAS-Data-Management/DIS-Foreign-key-definition-in-table-is-incomplete/m-p/325537#M9480 <P>I think the main point is the line:&nbsp;<EM>The&nbsp;foreign&nbsp;key PAYMENT.AXORDER is invalid because it has no partner unique key. It is recommended that the key be deleted.</EM></P> <P>&nbsp;</P> <P>I don't know if you missed a bit from what you pasted - I can't see the integrity constraints listed.</P> <P>&nbsp;</P> <P>I don't have DI available to me right now, so I'm suggesting this over the top off my head. Can you open a code editor from the Tools menu and type in&nbsp;</P> <PRE><CODE class=" language-sas">proc contents data=STG0TRAX.TRAX_AXORDER details; run;</CODE></PRE> <P>That&nbsp;<EM>details</EM> is the crucial bit - it forces&nbsp;<EM>contents</EM> to show index and constraint information at the bottom of each table listing. I'm expecting to see something like:</P> <P><IMG src="https://communities.sas.com/t5/image/serverpage/image-id/6774i7783ACC7C461BACA/image-size/original?v=v2&amp;px=-1" border="0" alt="Screen Shot 2017-01-18 at 2.42.17 PM.png" title="Screen Shot 2017-01-18 at 2.42.17 PM.png" /></P> <P>Additionally if you look at the generated code for the&nbsp;<EM>create table</EM> you may see something which refers to the foreign key.</P> Wed, 18 Jan 2017 01:50:18 GMT LaurieF 2017-01-18T01:50:18Z https://communities.sas.com/t5/SAS-Data-Management/DIS-Foreign-key-definition-in-table-is-incomplete/m-p/325126#M9407 <P>Hi,</P><P>&nbsp;</P><P>I m getting this error "&nbsp;Foreign key definition in table is incomplete" and I dont know why. I don't even know what the issue is. A solution that works is to delete the table and register it again, but that is very tedious, since quite a lot of tables are broken.</P><P>&nbsp;</P><P>I am using Data Integration Studio 4.9 and SAS 9.4,</P><P>&nbsp;</P><P>&nbsp;</P><P><IMG src="https://communities.sas.com/t5/image/serverpage/image-id/6748i5B41447FFEB4DAEC/image-size/original?v=v2&amp;px=-1" border="0" alt="dis-fkey.JPG" title="dis-fkey.JPG" /></P><P>&nbsp;</P><P>Can anyone help me out please?</P><P>&nbsp;</P><P>Thanks,</P><P>Dirk</P> Mon, 16 Jan 2017 20:54:54 GMT https://communities.sas.com/t5/SAS-Data-Management/DIS-Foreign-key-definition-in-table-is-incomplete/m-p/325126#M9407 dirks 2017-01-16T20:54:54Z https://communities.sas.com/t5/SAS-Data-Management/DIS-Foreign-key-definition-in-table-is-incomplete/m-p/325157#M9408 <P>Can you show what the definition of the table is? If the table already exists and is causing an error when you load into it, there is probably a difference between the current definition of the table and what the&nbsp;<EM>create table</EM> node is attempting to do with it.</P> <P>&nbsp;</P> <P>Can you let us know what the foreign key definition is within <EM>create table </EM>and the metadata for both&nbsp;<EM>orderpiece&nbsp;</EM>and&nbsp;<EM>trax_orderpiece</EM>? and how the table is currently physically defined? (<EM>proc contents</EM> will show it - right-click the table node, selection <EM>Analyze&nbsp;</EM>then&nbsp;<EM>Contents</EM>)</P> Tue, 17 Jan 2017 00:39:17 GMT https://communities.sas.com/t5/SAS-Data-Management/DIS-Foreign-key-definition-in-table-is-incomplete/m-p/325157#M9408 LaurieF 2017-01-17T00:39:17Z https://communities.sas.com/t5/SAS-Data-Management/DIS-Foreign-key-definition-in-table-is-incomplete/m-p/325215#M9409 <P>If I click through the tabs of the Create&nbsp;Table&nbsp;transformation, this error pops up:</P><P>&nbsp;</P><P>The&nbsp;foreign&nbsp;key PAYMENT.AXORDER is invalid because it has no partner unique key. It is recommended that the key be deleted.</P><P>&nbsp;</P><P>&nbsp;</P><P>This is the content of AXORDER:</P><P>&nbsp;</P><P>&nbsp;</P><PRE> Das SAS System 10:52 Tuesday, January 17, 2017 1 Die Prozedur CONTENTS Dateiname TRAXPROD.AXORDER Beobachtungen . Membertyp DATA Variablen 62 Engine ORACLE Indizes 0 Erstellt . Beobachtungslänge 0 Zuletzt geändert . Gelöschte Beobachtungen 0 Schutz Komprimiert NEIN Dateityp Sortiert NEIN Etikett Datendarstellung Standard Codierung Default Alphabetische Liste der Variablen und Attribute # Variable Typ Länge Format Einleseformat Etikett 1 AXORDERID Char 9 $9. $9. AXORDERID 29 BANKPERMIT Char 1 $1. $1. BANKPERMIT 30 COLLECTIV_INVOICE Char 1 $1. $1. COLLECTIV_INVOICE 56 CONADDITIONAL Char 255 $255. $255. CONADDITIONAL 48 CONADDRESSID Char 9 $9. $9. CONADDRESSID 51 CONCITY Char 255 $255. $255. CONCITY 58 CONCOUNTRY Char 2 $2. $2. CONCOUNTRY 57 CONDEPARTMENT Char 128 $128. $128. CONDEPARTMENT 54 CONEMAIL Char 4000 $4000. $4000. CONEMAIL 53 CONFAX Char 36 $36. $36. CONFAX 55 CONMOBIL Char 36 $36. $36. CONMOBIL 52 CONPHONE Char 36 $36. $36. CONPHONE 46 CONSIGNEE Char 255 $255. $255. CONSIGNEE 47 CONSIGNEEADD Char 255 $255. $255. CONSIGNEEADD 61 CONSIGNEENO Char 32 $32. $32. CONSIGNEENO 49 CONSTREET Char 255 $255. $255. CONSTREET 50 CONZIP Char 36 $36. $36. CONZIP 11 CREATED Num 8 DATETIME20. DATETIME20. CREATED 13 CUSTOMER Char 255 $255. $255. CUSTOMER 14 CUSTOMERADD Char 255 $255. $255. CUSTOMERADD 6 CUSTOMERREF Char 128 $128. $128. CUSTOMERREF 32 EUROCURRENCY Num 8 EUROCURRENCY 23 INVADDITIONAL Char 255 $255. $255. INVADDITIONAL 15 INVADDRESSID Char 9 $9. $9. INVADDRESSID 18 INVCITY Char 255 $255. $255. INVCITY 26 INVCONTACT Char 255 $255. $255. INVCONTACT 25 INVCOUNTRY Char 2 $2. $2. INVCOUNTRY 24 INVDEPARTMENT Char 128 $128. $128. INVDEPARTMENT 21 INVEMAIL Char 4000 $4000. $4000. INVEMAIL 20 INVFAX Char 36 $36. $36. INVFAX 22 INVMOBIL Char 36 $36. $36. INVMOBIL 59 INVNO Char 32 $32. $32. INVNO 9 INVOICE Char 1 $1. $1. INVOICE 19 INVPHONE Char 36 $36. $36. INVPHONE 16 INVSTREET Char 255 $255. $255. INVSTREET 17 INVZIP Char 36 $36. $36. INVZIP 31 ISOCURRENCY Char 3 $3. $3. ISOCURRENCY 4 ORDERDATE Num 8 DATETIME20. DATETIME20. ORDERDATE 2 ORDERID Char 9 $9. $9. ORDERID 3 ORDERNR Char 32 $32. $32. ORDERNR Das SAS System 10:52 Tuesday, January 17, 2017 2 Die Prozedur CONTENTS Alphabetische Liste der Variablen und Attribute # Variable Typ Länge Format Einleseformat Etikett 62 PAYMENTID Char 9 $9. $9. PAYMENTID 7 PAYMENTREF Char 255 $255. $255. PAYMENTREF 8 PRODUCT Char 50 $50. $50. PRODUCT 12 READOUT Num 8 DATETIME20. DATETIME20. READOUT 28 SAP Char 20 $20. $20. SAP 5 SAPVATCODE Char 4 $4. $4. SAPVATCODE 33 SHIPPER Char 255 $255. $255. SHIPPER 34 SHIPPERADD Char 255 $255. $255. SHIPPERADD 60 SHIPPERNO Char 32 $32. $32. SHIPPERNO 43 SPADDITIONAL Char 255 $255. $255. SPADDITIONAL 35 SPADDRESSID Char 9 $9. $9. SPADDRESSID 38 SPCITY Char 255 $255. $255. SPCITY 45 SPCOUNTRY Char 2 $2. $2. SPCOUNTRY 44 SPDEPARTMENT Char 128 $128. $128. SPDEPARTMENT 41 SPEMAIL Char 4000 $4000. $4000. SPEMAIL 40 SPFAX Char 36 $36. $36. SPFAX 42 SPMOBIL Char 36 $36. $36. SPMOBIL 39 SPPHONE Char 36 $36. $36. SPPHONE 36 SPSTREET Char 255 $255. $255. SPSTREET 37 SPZIP Char 36 $36. $36. SPZIP 10 TEXT Char 1000 $1000. $1000. TEXT 27 VATID Char 20 $20. $20. VATID </PRE><P>&nbsp;</P><P>And this is the result table:</P><P>&nbsp;</P><PRE> Das SAS System 10:53 Tuesday, January 17, 2017 1 Die Prozedur CONTENTS Dateiname STG0TRAX.TRAX_AXORDER Beobachtungen 91708 Membertyp DATA Variablen 6 Engine BASE Indizes 0 Erstellt 01/17/2017 01:05:49 Beobachtungslänge 56 Zuletzt geändert 01/17/2017 01:05:49 Gelöschte Beobachtungen 0 Schutz Komprimiert NEIN Dateityp Sortiert NEIN Etikett Datendarstellung WINDOWS_64 Codierung wlatin1 Western (Windows) Engine/Hostabhängige Informationen Dateiseitengröße 65536 Anzahl der Dateiseiten 79 Erste Datenseite 1 Max. Anz. Beob. pro Seite 1167 Anz. Beob. auf erster Datenseite 1137 Anzahl der Dateireparaturen 0 ExtendObsCounter YES Dateiname D:\DWH\Prod\Data\Staging\Stage0\Startrax\trax_axorder.sas7bdat Erstellt mit Release 9.0401M2 Erstellt mit Betriebssystem X64_SRV12 Alphabetische Liste der Variablen und Attribute # Variable Typ Länge Format Einleseformat Etikett 2 AXORDERID Char 9 $9. $9. AXORDERID 5 CREATED Num 8 DATETIME20. DATETIME20. CREATED 1 ETL_Datetime Num 8 DATETIME20. 3 ORDERID Char 9 $9. $9. ORDERID 4 ORDERNR Char 14 $14. $14. ORDERNR 6 READOUT Num 8 DATETIME20. DATETIME20. READOUT </PRE> Tue, 17 Jan 2017 09:54:28 GMT https://communities.sas.com/t5/SAS-Data-Management/DIS-Foreign-key-definition-in-table-is-incomplete/m-p/325215#M9409 dirks 2017-01-17T09:54:28Z https://communities.sas.com/t5/SAS-Data-Management/DIS-Foreign-key-definition-in-table-is-incomplete/m-p/325243#M9410 <P>This seems like a metadata error rather than a physical table problem.</P> <P>What does the Keys tab for your target table look like?</P> Tue, 17 Jan 2017 11:21:59 GMT https://communities.sas.com/t5/SAS-Data-Management/DIS-Foreign-key-definition-in-table-is-incomplete/m-p/325243#M9410 LinusH 2017-01-17T11:21:59Z https://communities.sas.com/t5/SAS-Data-Management/DIS-Foreign-key-definition-in-table-is-incomplete/m-p/325253#M9411 <P>Linus,</P><P>&nbsp;</P><P>the key tab is empty.</P><P>&nbsp;</P><P>Dirk</P> Tue, 17 Jan 2017 12:08:02 GMT https://communities.sas.com/t5/SAS-Data-Management/DIS-Foreign-key-definition-in-table-is-incomplete/m-p/325253#M9411 dirks 2017-01-17T12:08:02Z https://communities.sas.com/t5/SAS-Data-Management/DIS-Foreign-key-definition-in-table-is-incomplete/m-p/325537#M9480 <P>I think the main point is the line:&nbsp;<EM>The&nbsp;foreign&nbsp;key PAYMENT.AXORDER is invalid because it has no partner unique key. It is recommended that the key be deleted.</EM></P> <P>&nbsp;</P> <P>I don't know if you missed a bit from what you pasted - I can't see the integrity constraints listed.</P> <P>&nbsp;</P> <P>I don't have DI available to me right now, so I'm suggesting this over the top off my head. Can you open a code editor from the Tools menu and type in&nbsp;</P> <PRE><CODE class=" language-sas">proc contents data=STG0TRAX.TRAX_AXORDER details; run;</CODE></PRE> <P>That&nbsp;<EM>details</EM> is the crucial bit - it forces&nbsp;<EM>contents</EM> to show index and constraint information at the bottom of each table listing. I'm expecting to see something like:</P> <P><IMG src="https://communities.sas.com/t5/image/serverpage/image-id/6774i7783ACC7C461BACA/image-size/original?v=v2&amp;px=-1" border="0" alt="Screen Shot 2017-01-18 at 2.42.17 PM.png" title="Screen Shot 2017-01-18 at 2.42.17 PM.png" /></P> <P>Additionally if you look at the generated code for the&nbsp;<EM>create table</EM> you may see something which refers to the foreign key.</P> Wed, 18 Jan 2017 01:50:18 GMT https://communities.sas.com/t5/SAS-Data-Management/DIS-Foreign-key-definition-in-table-is-incomplete/m-p/325537#M9480 LaurieF 2017-01-18T01:50:18Z https://communities.sas.com/t5/SAS-Data-Management/DIS-Foreign-key-definition-in-table-is-incomplete/m-p/325622#M9482 <P>I went through the&nbsp;Keys tab of my source tables and I saw the foreign keys there. Once I clicked on them, I got an error message and it was suggested to delete them.</P><P>&nbsp;</P><P>I went through all tables and deleted the missing keys and now it is working properly.</P><P>&nbsp;</P><P>Thanks everyone!</P> Wed, 18 Jan 2017 11:21:51 GMT https://communities.sas.com/t5/SAS-Data-Management/DIS-Foreign-key-definition-in-table-is-incomplete/m-p/325622#M9482 dirks 2017-01-18T11:21:51Z