The Categories table in MySQL Northwind database

The Categories table lists product categories. Please pay attention to the following attributes in this table.

PRIMARY KEY is CategoryID and it's auto incremented. CategoryID is also a column in Products table as a foreign key column.
Unique key Uidx_categories_category_name is created to enforce uniqueness on category names.
Character type columns are defined as UTF8 to allow non English characters to be stored.
VARCHAR columns are defined as NOT NULL with a DEFAUTL constraint ''.
The Picture column only stores the name of the image file. The actual images are to be stored on a drive or server which can referred to dynamically by your application program such as PHP.

Data view of Categories table

Categories table

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

-- Create table `categories`
 
DROP TABLE IF EXISTS `categories`;
 
CREATE TABLE `categories` (
  `CategoryID` tinyint(5) unsigned NOT NULL AUTO_INCREMENT,
  `CategoryName` varchar(15) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `Description` mediumtext CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `Picture` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  PRIMARY KEY (`CategoryID`),
  UNIQUE KEY `Uidx_categories_category_name` (`CategoryName`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;
 
-- Add data for the table `categories`
 
insert  into `categories`(`CategoryID`,`CategoryName`,`Description`,`Picture`) 
values (1,'Beverages','Soft drinks, coffees, teas, beers, and ales','beverages.gif'),
(2,'Condiments','Sweet and savory sauces, relishes, spreads, and seasonings','condiments.gif'),
(3,'Confections','Desserts, candies, and sweet breads','confections.gif'),
(4,'Dairy Products','Cheeses','diary.gif'),
(5,'Grains/Cereals','Breads, crackers, pasta, and cereal','cereals.gif'),
(6,'Meat/Poultry','Prepared meats','meat.gif'),
(7,'Produce','Dried fruit and bean curd','produce.gif'),
(8,'Seafood','Seaweed and fish','seafood.gif');

Happy Coding!

Other tutorials in this category

1. What is Northwind database in MySQL

2. Create Northwind database in MySQL

3. The Suppliers 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

	Custom Search