• PRIMARY KEY is ProductID and it's auto incremented. ProductID is also a column in Order_Details table as a foreign key column.
• VARCHAR columns are defined as NOT NULL with a DEFAUTL constraint ''.
• Foreign key (FK_products_categoryid) is defined on CategoryID column which references CategoryID column in Categories table.
• Foreign key (FK_products_supplierid) is defined on SupplierID column which references SupplierID column in SupplierID table.
• Indexes (`idx_FK_products_categoryid` and `idx_FK_products_supplierid`) are created for the foreign key columns. The aim of the indexing is to improve query performance when the columns are joined on the two tables.
• ProductName has a index defined to improve query performance on product name.
• Character type columns are defined as UTF8 to allow non English characters to be stored.
• Discontinued column is defined as ENUM data type with two values only allowed: y for yes and n for no. ENUM simulates a check constraint by using syntax enum('y','n'), it restricts values in this column to be either 'y' for yes and 'n' for no.

  When we insert or update a product, we can simply use the character 'y' or 'n' to get the job done.

  update products set discontinued = 'y' where ProductID = 1;

  An ENUM is a string object with a value chosen from a list of permitted values that are enumerated explicitly in the column specification at table creation time.

  Note that if you insert 'Y' or 'N', MySQL will automatically convert 'Y' to 'y', 'N' to 'n', so enum data type is not case-sensitive by default.

  If the strict SQL mode is enabled, MySQL generates an error when any other values are saved to this column. In non-strict SQL mode, an empty string '' is inserted when invalid ENUM value is used in insert statement.

  Prior to MySQL 8.0.16, CREATE TABLE permits CHECK constraint syntax, it's parsed but didn't enforced the CHECK constraint. As of MySQL 8.0.16, CHECK constraint is supported as a starndard feature for all storage engines.

Data view of Products table

To create Order_Details table, run the following CREATE and INSERT INTO statement.

USE `northwind`;
 
-- Table structure for table `products`
 
DROP TABLE IF EXISTS `products`;
 
CREATE TABLE `products` (
  `ProductID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `ProductName` varchar(40) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `SupplierID` int(10) unsigned NOT NULL,
  `CategoryID` tinyint(5) unsigned NOT NULL,
  `QuantityPerUnit` varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `UnitPrice` double NOT NULL DEFAULT '0',
  `UnitsInStock` smallint(5) unsigned NOT NULL DEFAULT '0',
  `UnitsOnOrder` smallint(5) unsigned NOT NULL DEFAULT '0',
  `ReorderLevel` smallint(5) unsigned NOT NULL DEFAULT '0',
  `Discontinued` enum('y','n') CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT 'n',
  PRIMARY KEY (`ProductID`),
  KEY `idx_FK_products_categoryid` (`CategoryID`),
  KEY `idx_FK_products_supplierid` (`SupplierID`),
  KEY `idx_products_product_name` (`ProductName`),
  CONSTRAINT `FK_products_categoryid` FOREIGN KEY (`CategoryID`) REFERENCES `categories` (`CategoryID`),
  CONSTRAINT `FK_products_supplierid` FOREIGN KEY (`SupplierID`) REFERENCES `suppliers` (`SupplierID`)
) ENGINE=InnoDB AUTO_INCREMENT=78 DEFAULT CHARSET=utf8;
 
--Data for the table `products`

Happy Coding!

Other tutorials in this category

1. What is Northwind database in MySQL

2. Create Northwind database in MySQL

3. The Categories table in MySQL Northwind database

4. The Suppliers table in MySQL Northwind database

5. The Customers table in MySQL Northwind database

6. The Employees table in MySQL Northwind database

7. The Shippers table in MySQL Northwind database

8. The Orders table in MySQL Northwind database

9. The Order Details table in MySQL Northwind database

10. SQL Views in MySQL Northwind database

Back to Tutorial Index Page