|
|
The Orders table in MySQL Northwind database
The Orders table stores information about each order such as the customer who placed the order, employee who handled the order,
order and shipment date, which shipper made the shipment, etc.
- PRIMARY KEY is OrderID and it's auto incremented. OrderID is also a column in Order_Details table as a foreign key column.
- 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.
- LastName and PostalCode column each has a index defined to improve query performance.
- Foreign key (FK_orders_customer_id) is defined on CustomerID column which references CustomerID column in Customers table.
- Foreign key (FK_orders_employeeid) is defined on EmployeeID column which references EmployeeID column in Employees table.
- Foreign key (FK_orders_shipvia) is defined on ShipVia column which references ShipperID column in Shippers 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.
- ShippedDate and ShipPostalCode have index defined on them to improve query performance when they are used in WHERE clause of a query.
Data view of Orders table
To create Order_Details table, run the following CREATE and INSERT INTO statement.
USE `northwind`;
DROP TABLE IF EXISTS `orders`;
CREATE TABLE `orders` (
`OrderID` int(10) unsigned NOT NULL AUTO_INCREMENT,
`CustomerID` varchar(5) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`EmployeeID` int(10) unsigned NOT NULL,
`OrderDate` datetime NOT NULL,
`RequiredDate` datetime DEFAULT NULL,
`ShippedDate` datetime DEFAULT NULL,
`ShipVia` int(10) unsigned NOT NULL,
`Freight` double NOT NULL DEFAULT '0',
`ShipName` varchar(40) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`ShipAddress` varchar(60) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`ShipCity` varchar(15) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`ShipRegion` varchar(15) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`ShipPostalCode` varchar(10) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`ShipCountry` varchar(15) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
PRIMARY KEY (`OrderID`),
KEY `idx_FK_orders_shipvia` (`ShipVia`),
KEY `idx_FK_orders_employeeid` (`EmployeeID`),
KEY `idx_orders_shipped_date` (`ShippedDate`),
KEY `idx_orders_ship_postalcode` (`ShipPostalCode`),
KEY `idx_FK_orders_customer_id` (`CustomerID`),
CONSTRAINT `FK_orders_customer_id` FOREIGN KEY (`CustomerID`) REFERENCES `customers` (`CustomerID`),
CONSTRAINT `FK_orders_employeeid` FOREIGN KEY (`EmployeeID`) REFERENCES `employees` (`EmployeeID`),
CONSTRAINT `FK_orders_shipvia` FOREIGN KEY (`ShipVia`) REFERENCES `shippers` (`ShipperID`)
) ENGINE=InnoDB AUTO_INCREMENT=11078 DEFAULT CHARSET=utf8;
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 Order Details table in MySQL Northwind database
10. SQL Views in MySQL Northwind database
Back to Tutorial Index Page
|
