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.
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