locked
This constraint cannot be enabled as not all values have corresponding parent values. RRS feed

  • Question

  • User944339287 posted

    Hi guys.. i having this error msg when i trying to return data from DB..

    i guess something wrong to my database record but wondering how can i found out which record got problem? (as i having 1000++ records right now)

    my database structure.

    Table Name: CATEGORY_GENERAL
    Table Structure: ID, ParentCatID, TYPE, NAME

    sample data:
    101 , 000 , parent , Smartphone
    102 , 000 , parent , Computer
    103 , 101 , child    , Samsung
    104 , 101 , child    , HTC
    105 , 101 , child    , Motorola
     

    Public Function return_category_menu() As DataSet
    
       Dim strParentCat As String
       Dim strChildCat As String
    
       strParentCat = "SELECT * FROM category_general WHERE type = 'parent'"
       strChildCat = "SELECT * FROM category_general WHERE cg.type = 'child'"
    
    
       Dim ParentCat As New OdbcDataAdapter(strParentCat, MyConn)
       Dim ChildCat As New OdbcDataAdapter(strChildCat, MyConn)
    
       'Add the DataTables to the DataSet
       Dim mydataset As New DataSet()
    
       Using MyConn
          MyConn.Open()
          ParentCat.Fill(mydataset, "ParentCategory")
          ChildCat.Fill(mydataset, "ChildCategory")
       End Using
    
       'Add a DataRelation
       mydataset.Relations.Add("Children", mydataset.Tables("ParentCategory").Columns("ID"), mydataset.Tables("ChildCategory").Columns("ParentCatID"))
    
       Return mydataset
    
    End Function

    Friday, September 5, 2014 5:50 AM

Answers

  • User269602965 posted

    Normally a parent - child relationship (1 to many) is handled as TWO tables, one parent with primary key, and one child with primary key and foreign key referencing the parent record.

    In this case you have ONE table that has a SELF-REFERENCING DESIGN within the table.

    To extract only the CHILD records that have a matching PARENT record

    SELECT
      a.ID AS PARENT_ID,
      c.ID AS CHILD_ID,
      c.TYPE,
      c.NAME
    FROM
      CATEGORY_GENERAL a
    LEFT OUTER JOIN
      (
      SELECT
        b.*
      FROM
        CATEGORY_GENERAL b
      WHERE
        TYPE = 'child'
      ) c
    ON a.ID = c.PARENTCATID
    WHERE
      a.TYPE = 'parent'
    /
    
     PARENT_ID   CHILD_ID TYPE             NAME
    ---------- ---------- ---------------- --------------------------------
           101        103 child            Samsung
           101        104 child            HTC
           101        105 child            Motorola
           102        106 child            Samsung
           102        107 child            Samsung

    These records have REFERENTIAL INTEGRITY with the PARENTS records. (GOOD RECORDS)

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, September 7, 2014 5:27 PM

All replies

  • User269602965 posted

    I would create a VIEW and do LEFT OUTER JOIN of Parent to Child and thus only records in the Parent table with matching Child records will appear.

    And display the VIEW records.

    I would also use BIND variables to pass values to your WHERE clause instead of hard coded values.

     

    Friday, September 5, 2014 3:39 PM
  • User944339287 posted

    Hi,

    Can you provide SQL statement for me? TQ

    Saturday, September 6, 2014 4:10 AM
  • User269602965 posted

    What Database are you using?

    Is there only one data table?

     

    Saturday, September 6, 2014 8:46 AM
  • User269602965 posted

    And what do you want your output table to look like?

    Saturday, September 6, 2014 8:47 AM
  • User944339287 posted

    Hi,

    I am using MySQL database. Yup. Only one table in my database.

    I just want to find out how to make sure my web app can returned data properly and no error come up.

    * so i need to find out which record got problem and remove it. 

    Sunday, September 7, 2014 2:43 AM
  • User269602965 posted

    Normally a parent - child relationship (1 to many) is handled as TWO tables, one parent with primary key, and one child with primary key and foreign key referencing the parent record.

    In this case you have ONE table that has a SELF-REFERENCING DESIGN within the table.

    To extract only the CHILD records that have a matching PARENT record

    SELECT
      a.ID AS PARENT_ID,
      c.ID AS CHILD_ID,
      c.TYPE,
      c.NAME
    FROM
      CATEGORY_GENERAL a
    LEFT OUTER JOIN
      (
      SELECT
        b.*
      FROM
        CATEGORY_GENERAL b
      WHERE
        TYPE = 'child'
      ) c
    ON a.ID = c.PARENTCATID
    WHERE
      a.TYPE = 'parent'
    /
    
     PARENT_ID   CHILD_ID TYPE             NAME
    ---------- ---------- ---------------- --------------------------------
           101        103 child            Samsung
           101        104 child            HTC
           101        105 child            Motorola
           102        106 child            Samsung
           102        107 child            Samsung

    These records have REFERENTIAL INTEGRITY with the PARENTS records. (GOOD RECORDS)

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, September 7, 2014 5:27 PM