|
|
The Suppliers table in MySQL Northwind database
The Suppliers table stores the companies who supply products to Northwind Traders. Please pay attention to the following attributes in this table.
- PRIMARY KEY is SupplierID and it's auto incremented. SupplierID is also a column in Products table as a foreign key column.
- VARCHAR columns are defined as NOT NULL with a DEFAUTL constraint ''.
- Index is created for CompanyName column (idx_suppliers_product_name) and PostalCode column (idx_suppliers_postalcode) to speed up queries
when the column is used in WHERE clause of a query to answer business questions.
- Character type columns are defined as UTF8 to allow non English characters to be stored.
Data view of Suppliers table
To create Order_Details table, run the following CREATE and INSERT INTO statement.
USE `northwind`;
DROP TABLE IF EXISTS `suppliers`;
CREATE TABLE `suppliers` (
`SupplierID` int(10) unsigned NOT NULL AUTO_INCREMENT,
`CompanyName` varchar(40) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`ContactName` varchar(30) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`ContactTitle` varchar(30) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`Address` varchar(60) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`City` varchar(15) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`Region` varchar(15) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`PostalCode` varchar(10) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`Country` varchar(15) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`Phone` varchar(24) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`Fax` varchar(24) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`HomePage` varchar(255) NOT NULL DEFAULT '',
PRIMARY KEY (`SupplierID`),
KEY `idx_suppliers_product_name` (`CompanyName`),
KEY `idx_suppliers_postalcode` (`PostalCode`)
) ENGINE=InnoDB AUTO_INCREMENT=30 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 Products 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
|
