I have a set of three tables, grandchild, parent and child - all was well.

But having found this set of tbales very difficult to integrate with the db, I have now realised I can't set up a PK/FK constraint. Some of the values in my (hoped_for) PK, must be null - unless I create imaginery values for them.

It seems my composite PK cannot have any column null even though, as a composite, the value is still unqiue.

Whats a guy meant to do lol

Should I create dopey data - eg setting a specifically NULL value?
should I resort to a surrogate key (oh pulleeeze lol),
or can I just be happy with a unique key being set.

Here's my data structure in case I have got it all wrong.


Code MySQL:
CREATE TABLE control_panel_data_groups
( control_panel_section varchar(99) collate utf8_unicode_ci NOT NULL
, cp_page_grouping varchar(32) collate utf8_unicode_ci NOT NULL default ''
, PRIMARY KEY  (control_panel_section,cp_page_grouping)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
 
 
CREATE TABLE control_panel_data_categories 
( control_panel_section varchar(99) collate utf8_unicode_ci NOT NULL default ''
, cp_page_grouping varchar(32) collate utf8_unicode_ci default NULL
, page_category varchar(99) collate utf8_unicode_ci NOT NULL
, KEY pageCategories_categoriesGroups_fk (control_panel_section, cp_page_grouping),
  KEY business_id_2 (control_panel_section, page_category)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
 
ALTER TABLE `control_panel_data_categories`
  ADD CONSTRAINT data_categories_data_groups_fk
    FOREIGN KEY (control_panel_section, cp_page_grouping) 
    REFERENCES control_panel_data_groups (control_panel_section, cp_page_grouping);
 
 
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 default NULL
, page_category varchar(99) collate utf8_unicode_ci default NULL
, UNIQUE KEY business_id ( business_id, control_panel_section, cp_page_grouping, page_category ),
  KEY control_panel_section ( control_panel_section, cp_page_grouping )
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
 
ALTER TABLE `control_panel_business_datagroups`
  ADD CONSTRAINT control_panel_business_datagroups_ibfk_1 
  FOREIGN KEY (control_panel_section, cp_page_grouping) 
  REFERENCES control_panel_data_categories (control_panel_section, cp_page_grouping),
  ADD CONSTRAINT cpBusinessDataGroups_businesses_fk 
  FOREIGN KEY (business_id) 
  REFERENCES businesses (id);