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

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

Answer 1

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.

 

Answer 2

User944339287 posted

Hi,

Can you provide SQL statement for me? TQ

Answer 3

User269602965 posted

What Database are you using?

Is there only one data table?

 

Answer 4

User269602965 posted

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

Answer 5

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. 

Answer 6

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)