[DIS] Foreign key definition in table is incomplete

[dirks]

Hi,

I m getting this error " 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.

I am using Data Integration Studio 4.9 and SAS 9.4,

Can anyone help me out please?

Thanks,

Dirk

[LaurieF]

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 create table node is attempting to do with it.

Can you let us know what the foreign key definition is within create table and the metadata for both orderpiece and trax_orderpiece? and how the table is currently physically defined? (proc contents will show it - right-click the table node, selection Analyze then Contents)

[dirks]

If I click through the tabs of the Create Table transformation, this error pops up:

The foreign key PAYMENT.AXORDER is invalid because it has no partner unique key. It is recommended that the key be deleted.

This is the content of AXORDER:

                                                           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            

And this is the result table:

                                                           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  

[LinusH]

This seems like a metadata error rather than a physical table problem.

What does the Keys tab for your target table look like?

[dirks]

Linus,

the key tab is empty.

Dirk

[LaurieF]

I think the main point is the line: The foreign key PAYMENT.AXORDER is invalid because it has no partner unique key. It is recommended that the key be deleted.

I don't know if you missed a bit from what you pasted - I can't see the integrity constraints listed.

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 

proc contents data=STG0TRAX.TRAX_AXORDER details;
run;

That details is the crucial bit - it forces contents to show index and constraint information at the bottom of each table listing. I'm expecting to see something like:

Additionally if you look at the generated code for the create table you may see something which refers to the foreign key.

[dirks]

I went through the 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.

I went through all tables and deleted the missing keys and now it is working properly.

Thanks everyone!