Foreign Key Coffee Question

[nomadsoul]

Hi,

On page 166 it say the non_coffee_products table has a many-to-one relation with non_coffee_categories table.

 

I'm guessing this is the FK in the non_coffee_products table:

KEY `non_coffee_category_id` (`non_coffee_category_id`)

?

 

But I see no relation to this table:

CREATE TABLE `non_coffee_categories` (
 `id` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
 `category` varchar(40) NOT NULL,
 `description` tinytext NOT NULL,
 `image` varchar(45) NOT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `category` (`category`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

Almost all my SQL experience is in T-SQL so maybe I just don't understand MySQL syntax. I'm probably missing the obvious (as usual with me) and pre-apologize if I am.

Thanks in advance for your help.

[Antonio Conte]

Almost all my SQL experience is in T-SQL so maybe I just don't understand MySQL syntax. I'm probably missing the obvious (as usual with me) and pre-apologize if I am.

Thanks in advance for your help.

 

Hey,

 

I'm not sure how T-SQL works. In MySqli, we don't define foreign keys in the table structure - we do it in our queries.

 

I don't know how the structure for table "non_coffee_products" is. The columns in the SELECT statement is just made up by me.

 

SELECT product.id, product.name, category.name

FROM non_coffee_products AS product

INNER JOIN non_coffee_categories AS category ON (product.category_id = category.id)

 

I don't know if T-SQL has Inner Joins, but I'm guessing they do. Hope it makes sense. Several FROM tables and a WHERE clause might also work if you don't need category names etc.

 

[Larry]

"KEY" is just another term for INDEX, so the CREATE statement creates an index on that column, as it's a foreign key to the other table. The purpose is to improve the performance of those JOINS.

 

You can, by the way, define foreign key constraints in the table structure, depending upon the version of MySQL in use, and if using the InnoDB storage engine.

[Antonio Conte]

Did not know that, Larry. Will check it out.

[nomadsoul]

Thanks Larry and Antonio, that cleared it up nicely.

 

So I'm guessing that inner joins were used a lot before MySQL became relational?

I heard that MySQL was not always relational.

[Larry]

I've been familiar with MySQL ever since it was lumped in with mSQL, and I've never heard of it not being relational. Also, joins are always for relational databases, regardless of the join type.