Foreign Keys On The Coffee Tables

[Rob_On_LU_Forum]

Thank you forum leaders in advance for reading my topic.

 

My question relates to the foreign keys on the non_coffee_products table shown in Figure 7.3

 

I can see that the non_coffee_category_id column is the foreign key referenced to non_coffee_category_id in the non_rope_categories table. ( I notice the downloadable sql script for the book does not include the FOREIGN KEY statements, or as far as I can tell they are not included).

 

So I added this foreign key as:

 

 

 

CREATE TABLE `non_coffee_products` (

  `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,

  `non_coffee_category_id` tinyint(3) unsigned NOT NULL,

  `name` varchar(60) NOT NULL,

  `description` tinytext,

  `image` varchar(45) NOT NULL,

  `price` decimal(5,2) unsigned NOT NULL,

  `stock` mediumint(8) unsigned NOT NULL DEFAULT '0',

  `date_created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,

  PRIMARY KEY (id),

  KEY non_coffee_category_id (non_coffee_category_id),

  FOREIGN KEY (non_coffee_category_id) REFERENCES non_coffee_categories (id)

  ON UPDATE CASCADE

  ON DELETE CASCADE

) ENGINE=MyISAM  DEFAULT CHARSET=utf8  ;

 

 

When I view this script as a EER Diagram in MySQL Workbench, it looks right to this newbie...

 

My question relates to the non_coffee_products and specific_coffee tables relationship to the sales table.

 

Which column in the sales table should be referenced as the foreign keys to the non_coffee_products and specific_coffees tables?

 

Once again thank you for reading my posts. It is very special in the communities we live in today that the forum leaders give their valued advice, for free and asking nothing in return. That is very special and I thank you.

 

 

 

 

 

 

 

 

 

[Larry]

Hello Rob. First, thanks for the nice words. It is appreciated. Second, foreign key constraints only work with InnoDB. They don't work with MyISAM. 

 

Anyway, in answer to your question, the product_id column in sales is a FK to id in non_coffee_products and to id in specific_coffees.

[Rob_On_LU_Forum]

Thanks for explanation Larry.  So would I be correct then to say that in order to set up the FK's on the coffee tables as described in the book, the tables would need to use InnoDB?

 

I notice the books ex2.sql database script does use MyISAM for the coffee tables but the chapter discusses using foreign keys on them, so this is what brought me to ask the question. 

 

 

[Larry]

Yes, that's correct. You'd use InnoDB to allow for FK constraints. 

 

Sorry for the confusion. I suspect I'm trying to differentiate between having FK (which is a database design issue) and using actual FK constraints.

[Rob_On_LU_Forum]

I see. Now I am on the same page. Thanks again, Rob