|
|
The Customers table in MySQL Northwind database
The Customers table stores corporate customers data of Northwind products.
- PRIMARY KEY is CustomerID and it's auto incremented. CustomerID is also a column in Orders table as a foreign key column.
- VARCHAR columns are defined as NOT NULL with a DEFAUTL constraint ''.
- Indexes are created on column CompanyName, City, Region, and PostCode. The aim of the indexing is to improve query performance
where there columns are used in WHERE clause of a query.
- Character type columns are defined as UTF8 to allow non English characters to be stored.
Data view of Customers table
To create Customers table, run the following CREATE and INSERT INTO statement.
USE `northwind`;
DROP TABLE IF EXISTS `customers`;
CREATE TABLE `customers` (
`CustomerID` varchar(5) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`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 'Unknown',
`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 '',
PRIMARY KEY (`CustomerID`),
KEY `idx_customers_company_name` (`CompanyName`),
KEY `idx_customers_city` (`City`),
KEY `idx_customers_region` (`Region`),
KEY `idx_customers_postalcode` (`PostalCode`)
) ENGINE=InnoDB 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 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
|
