Got my self in bother?

Databases
1

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 :rolleyes: 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. :frowning:


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);
2

two suggestions –

first, forums threads are not the place to practice “link bait”

a thread title like “got my self in bother?” gives no clue as to what the thread is about, forcing people to actually open the thread to find out, and you can be sure that some percentage of potential repliers will not bother, whether purposefully, or because they simply missed it because they’re scanning the list of thread titles looking for certain words like query, primary key, and so on

second, you neglected to point out ~why~ some of your columns need to be NULL, leaving the onus on the person reading your post to try to guess what’s going on

helps?

:smiley:

3

oops. sorry. was very frustrated and forgot about making the title meaningful.

I think I have made progress by making the nulls sensible values. Just goes to show how when I think I have the db structured well, there is room for improvement.

bazz

4

unfortunately, “dopey” values (as you said earlier) don’t usually mean anything, and can lead to more trouble than they’re worth

e.g. “9999-12-31” as expiry date

what kind of “sensible” values did you settle on?

5

I don’t know why I got so exasperated.

All it took was for me to split the data into an extra table so that those which FK’d were primary keys.

:rolleyes:

6

looks like my work here is done :slight_smile: