• PRIMARY KEY is ID column, and it's auto incremented. To have its own ID column enables the possibility of extend Order_Details table to be used in another table as a single-column foreigh key.
• A composite unique key `Uidx_OrderID_ProductID` is created 2 columns `OrderID` and `ProductID`.
• VARCHAR columns are defined as NOT NULL with a DEFAUTL constraint ''.
• Character type columns are defined as UTF8 to allow non English characters to be stored.
• Foreign key (FK_order_details_orderid) is defined on OrderID column which references OrderID column in Orders table.
• Foreign key (FK_order_details_productid) is defined on ProductID column which references ProductID column in Products table.
• Indexes 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.

Data view of Order_Details table

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

USE `northwind`;
 
-- Table structure for table `order_details`
 
DROP TABLE IF EXISTS `order_details`;
 
CREATE TABLE `order_details` (
  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `OrderID` int(10) unsigned NOT NULL,
  `ProductID` int(10) unsigned NOT NULL,
  `UnitPrice` double unsigned NOT NULL DEFAULT '0',
  `Quantity` smallint(5) unsigned NOT NULL DEFAULT '1',
  `Discount` float unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`),
  UNIQUE KEY `Uidx_OrderID_ProductID` (`OrderID`,`ProductID`),
  KEY `idx_FK_order_details_productid` (`ProductID`),
  CONSTRAINT `FK_order_details_orderid` FOREIGN KEY (`OrderID`) REFERENCES `orders` (`OrderID`),
  CONSTRAINT `FK_order_details_productid` FOREIGN KEY (`ProductID`) REFERENCES `products` (`ProductID`)
) ENGINE=InnoDB AUTO_INCREMENT=2156 DEFAULT CHARSET=utf8;
 
-- Data for the table `order_details`

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 Products table in MySQL Northwind database

6. The Customers table in MySQL Northwind database

7. The Employees table in MySQL Northwind database

8. The Shippers table in MySQL Northwind database

9. The Orders table in MySQL Northwind database

10. SQL Views in MySQL Northwind database

Back to Tutorial Index Page