Integrity constraint violation - no parent

Anonymous
2010-04-13
2014-01-19
  • Anonymous - 2010-04-13

    I have the following tables:

    CREATE MEMORY TABLE APPLICATIONCI(
        PRODUCTNAME VARCHAR(255) NOT NULL,
        PRODUCTVERSION VARCHAR(255) NOT NULL,
        VENDOR VARCHAR(255) NOT NULL,
        DESCRIPTION VARCHAR(255),
        LICENSE_ID INTEGER,
        PRIMARY KEY(PRODUCTNAME,PRODUCTVERSION,VENDOR))
    CREATE MEMORY TABLE MACHINECI(
        SERIAL VARCHAR(255) NOT NULL PRIMARY KEY,
        CPUTYPE VARCHAR(255) NOT NULL,
        ECN VARCHAR(255) NOT NULL,
        MAKE VARCHAR(255) NOT NULL,
        MEMORY VARCHAR(255) NOT NULL,
        MODEL VARCHAR(255) NOT NULL,
        NUMBEROFCPUS INTEGER NOT NULL)
    CREATE MEMORY TABLE APPLICATIONINSTALLATION(
        SERIAL VARCHAR(255) NOT NULL,
        VENDOR VARCHAR(255) NOT NULL,
        PRODUCTNAME VARCHAR(255) NOT NULL,
        PRODUCTVERSION VARCHAR(255) NOT NULL,
        CONSTRAINT FKDBBC178AA1D77B74 FOREIGN KEY(PRODUCTNAME,PRODUCTVERSION,VENDOR) REFERENCES APPLICATIONCI(PRODUCTNAME,PRODUCTVERSION,VENDOR))
    ALTER TABLE APPLICATIONINSTALLATION ADD CONSTRAINT FKDBBC178AEAFDF5D4 FOREIGN KEY(SERIAL) REFERENCES MACHINECI(SERIAL)
    

    And when I try to:

    INSERT INTO APPLICATIONINSTALLATION (SERIAL,PRODUCTNAME,PRODUCTVERSION,VENDOR) VALUES ('sd34-fsd4-ff23-fdsf','foo','baa','foobaa')
    

    I get the following HSQL error:
    Integrity constraint violation - no parent FKDBBC178AA1D77B74 table: APPLICATIONCI / Error Code: -177 / State: 23000

    This is occurring in HSQL 1.8.0 packed in JBoss 5.1

    Any ideas?

     
  • Fred Toussi

    Fred Toussi - 2010-04-13

    You need an existing row in MACHINECI with the same value in the SERIAL column as the value you are attempting to insert into the other table.

     
  • Anonymous - 2010-04-13

    Fredt,

    Thanks for the reply. Sorry, I should have been more explicit. The values for both MACHINECI and APPLICATIONCI already exist by the time I try to insert a row in APPLICATIONINSTALLATION.

    INSERT INTO APPLICATIONCI (VENDOR,PRODUCTNAME,PRODUCTVERSION,DESCRIPTION,LICENSE_ID) VALUES ('foobaa','foo','baa','bla bla',2)
    INSERT INTO MACHINECI (SERIAL,CPUTYPE,ECN,MAKE,MEMORY,MODEL,NUMBEROFCPUS) VALUES ('sd34-fsd4-ff23-fdsf','Intel i7','hj234hty','IBM','192Gb','x3850',48)
    
     
  • Fred Toussi

    Fred Toussi - 2010-04-13

    I've just tested this with 1.8.1.2 and it works fine if I insert the following line before your statement.
    INSERT INTO APPLICATIONCI (VENDOR,PRODUCTNAME,PRODUCTVERSION,DESCRIPTION,LICENSE_ID) VALUES ('sd34-fsd4-ff23-fdsf','foo','baa','bla bla',2)

    Please check again. In case there is an issue with the particular jar, you can use the 1.8.1.2 jar instead of the one that is in the JBoss 5.1 package.

     
  • Fred Toussi

    Fred Toussi - 2010-04-13

    Last time I just inserted a modified version of the two insert statements you posted. Noticed they are required before your original INSERT problem. So this time I ran all three inserts exactly as you reported. They work fine.

     
  • Anonymous - 2010-04-13

    Fredt,

    Yes, you are correct. I've quickly changed my configuration to PostgreSQL and I got the same problem. The error was way more informative and I was able to fix it. Basically, the tables are actually being created by EJB3 entities and when generated the following was not correct:

    CREATE MEMORY TABLE APPLICATIONINSTALLATION( 
        SERIAL VARCHAR(255) NOT NULL, 
        VENDOR VARCHAR(255) NOT NULL, 
        PRODUCTNAME VARCHAR(255) NOT NULL, 
        PRODUCTVERSION VARCHAR(255) NOT NULL, 
        CONSTRAINT FKDBBC178AA1D77B74 FOREIGN KEY(VENDOR,PRODUCTNAME,PRODUCTVERSION) REFERENCES APPLICATIONCI(PRODUCTNAME,PRODUCTVERSION,VENDOR))
    The error is in the order of the FK fields:
     FOREIGN KEY(VENDOR,PRODUCTNAME,PRODUCTVERSION) should read  FOREIGN KEY(PRODUCTNAME,PRODUCTVERSION,VENDOR) like I have on my side test.
    

    The fix was to have the order correct in the Entity bean

    ...
    @JoinTable(name="ApplicationInstallation",
        joinColumns={@JoinColumn(name="serial")},
        inverseJoinColumns={@JoinColumn(name="productName"),@JoinColumn(name="productVersion"),@JoinColumn(name="vendor")})
    ...
    

    I've tested the fix once again on HSQL and everything is working fine.

    Again, thank you for your help.

     
  • Fred Toussi

    Fred Toussi - 2010-04-13

    OK. btw HSQLDB version 2.0 supports FK declarations with alternative column orders.

     

Log in to post a comment.

Get latest updates about Open Source Projects, Conferences and News.

Sign up for the SourceForge newsletter:





No, thanks