SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    fk difficulties.

    Hi been away for a while and must be rusty. And the forum has all changed

    what query should I build, to find composite FKs in the child, that are not in the parent table?

    I have two grandparent tables, a parent table and a child table.

    The parent table is successfully constrained to the two grandparent tables.
    The parent table makes a PK out of the two foreign keys
    The child table uses the pk from its parent along with an additional column, to make a new PK.
    The child table will not constrain to the parent table
    The child table WILL constrain to the grandparent tables just like its parent does.

    I'm using natural composite keys; not numerical keys.

    So I think this means that the combination (composite key) in the child table, is not in the parent

    I have tried the following query to identify rows in the child table that are not in its parent but it returns zero results.


    Code MySQL:
    SELECT child.business_id
         , child.control_panel_section
         , child.cp_page_grouping
         , child.page_category
         , child.business_type
         , child.business_sub_type
      FROM control_panel_business_datagroups as child
    left outer
      join control_panel_data_business_sub_types as parent
        on parent.control_panel_section = child.control_panel_section 
    where child.sub_type is null

    here's the create table statement for the child table.

    Code MySQL:
    CREATE TABLE control_panel_business_datagroups 
    ( business_id int(11) NOT NULL
    , control_panel_section varchar(99) collate utf8_unicode_ci NOT NULL
    , cp_page_grouping varchar(32) collate utf8_unicode_ci NOT NULL default ''
    , page_category varchar(99) collate utf8_unicode_ci NOT NULL default ''
    , business_type varchar(25) collate utf8_unicode_ci NOT NULL
    , business_sub_type varchar(25) collate utf8_unicode_ci NOT NULL
    , on_off char(3) collate utf8_unicode_ci NOT NULL default 'yes'
    , PRIMARY KEY  (business_id,control_panel_section,cp_page_grouping,page_category)
    , KEY cpBusinessDataGroups_cpDataCategories_fk (control_panel_section,cp_page_grouping,page_category)
    , KEY controlPanelBusinessDataGroups_cpBusinessSubTypeLookUps_fk (business_type,business_sub_type)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
     
    --
    -- Constraints for dumped tables
    --
     
    --
    -- Constraints for table control_panel_business_datagroups
    --
    ALTER TABLE control_panel_business_datagroups
      ADD CONSTRAINT controlPanelBusinessDataGroups_cpBusinessSubTypeLookUps_fk 
        FOREIGN KEY ( business_type
                          , business_sub_type) 
          REFERENCES business_sub_types_look_up ( business_type
                                                                   , sub_type)
    , ADD CONSTRAINT cpBusinessDataGroups_businesses_fk 
        FOREIGN KEY (business_id) 
          REFERENCES businesses (id) ON DELETE CASCADE
    , ADD CONSTRAINT cpBusinessDataGroups_cpDataCategories_fk 
        FOREIGN KEY ( control_panel_section
                          , cp_page_grouping
                          , page_category) 
          REFERENCES control_panel_data_categories ( control_panel_section
                                                                      , cp_page_grouping
                                                                      , page_category) 
                        ON DELETE CASCADE ON UPDATE CASCADE;

    The column names are the same in the child as in the parent.

    bazz

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    something is amiss

    in your query, you identify control_panel_business_datagroups as the child table, and then specify WHERE child.sub_type IS NULL

    but that table doesn't have a column called sub_type, so that query ~couldn't~ have worked

    and what did you mean by "the child table will not constrain to the parent table"?

    did you get an error message?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Oops, I was trying two queries, one to check the parent for null and the other for seeing if the child was null

    Code MySQL:
    SELECT child.business_id
         , child.control_panel_section
         , child.cp_page_grouping
         , child.page_category
         , child.business_type
         , child.business_sub_type
      FROM control_panel_business_datagroups as child
    left outer
      join control_panel_data_business_sub_types as parent
        on parent.control_panel_section = child.control_panel_section 
    where parent.sub_type is null

    the other was:
    where child.business_sub_type is null

    both return zero results.

    here is the constraint coding.

    Code MySQL:
    ALTER TABLE `control_panel_business_datagroups`
      ADD CONSTRAINT cpBusinessDataGroups_cpDataBusinessSubTypes_fk 
        FOREIGN KEY (control_panel_section, cp_page_grouping, page_category, business_type, business_sub_type)
          REFERENCES control_panel_data_business_sub_types( control_panel_section, cp_page_grouping, page_category, business_type, sub_type) ON UPDATE CASCADE ON DELETE CASCADE

    when trying the add that constraint, I get the following error

    #1452 - Cannot add or update a child row: a foreign key constraint fails (`main_database/#sql-bab_21ca37`, CONSTRAINT `cpBusinessDataGroups_cpDataBusinessSubTypes_fk` FOREIGN KEY (`control_panel_section`, `cp_page_grouping`, `page_category`, `business_type`, `busine)

  4. #4
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I finally got the "grey matter" to work.

    This query gave me the desired results and showed me the error of my ways.

    Code MySQL:
    select control_panel_section
    , cp_page_grouping
    , page_category
    , business_type
    , business_sub_type
    from control_panel_business_datagroups
    where 
    ( control_panel_section
    , cp_page_grouping
    , page_category
    , business_type
    , business_sub_type
    )
    not in 
    (SELECT 
    control_panel_section
    , cp_page_grouping
    , page_category
    , business_type
    , sub_type
    FROM control_panel_data_business_sub_types)

    all tidied up now and foreign key constraint added, successfully.

    Thanks rudy. just by responding you made me compete to get the answer. lol

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    you're welcome
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •