Fk difficulties

Databases
1

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

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.


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.


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

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?

3

Oops, I was trying two queries, one to check the parent for null and the other for seeing if the child was null


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.


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

I finally got the “grey matter” to work.

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


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 :slight_smile: and foreign key constraint added, successfully.

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

5

you’re welcome :slight_smile: