|
The Employees table in MySQL Northwind database
The Employees table stores staff data for people who work for the company.
- PRIMARY KEY is EmployeeID and it's auto incremented. EmployeeID is also a column in Orders table as a foreign key column.
- VARCHAR columns are defined as NOT NULL with a DEFAUTL constraint ''.
- Foreign key (FK_employees_reports_to) is defined on ReportsTo column which references EmployeeID column in Employees table.
This is a self-referencing foreign key where a column references another column in the same table.
- Indexes `idx_ReportsTo` is created for the foreign key column. The aim of the
indexing is to improve query performance when the column is used in a join on the same table.
- LastName and PostalCode column each has a index defined to improve query performance.
- Character type columns are defined as UTF8 to allow non English characters to be stored.
Data view of Employees table
To create Employees table, run the following CREATE and INSERT INTO statement.
USE `northwind`;
DROP TABLE IF EXISTS `employees`; CREATE TABLE `employees` ( `EmployeeID` int(10) unsigned NOT NULL AUTO_INCREMENT, `LastName` varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '', `FirstName` varchar(10) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '', `Title` varchar(30) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '', `TitleOfCourtesy` varchar(25) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '', `BirthDate` datetime NOT NULL, `HireDate` datetime NOT NULL, `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 '', `HomePhone` varchar(24) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '', `Extension` varchar(4) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '', `Photo` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '', `Notes` mediumtext CHARACTER SET utf8 COLLATE utf8_unicode_ci, `ReportsTo` int(10) unsigned DEFAULT NULL, PRIMARY KEY (`EmployeeID`), KEY `idx_employees_lastname` (`LastName`), KEY `idx_employees_postalcode` (`PostalCode`), KEY `idx_ReportsTo` (`ReportsTo`), CONSTRAINT `FK_employees_reports_to` FOREIGN KEY (`ReportsTo`) REFERENCES `employees` (`EmployeeID`) ) ENGINE=InnoDB AUTO_INCREMENT=10 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 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
|