36 lines
1.4 KiB
SQL
36 lines
1.4 KiB
SQL
-- データベースの作成
|
||
DROP DATABASE IF EXISTS `em_oop_db`;
|
||
CREATE DATABASE `em_oop_db` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
|
||
|
||
-- ユーザ権限付与
|
||
GRANT ALL PRIVILEGES ON `em_oop_db`.* TO 'rezouser'@'localhost';
|
||
|
||
-- データベースの選択
|
||
USE `em_oop_db`;
|
||
|
||
-- カテゴリテーブルの作成
|
||
CREATE TABLE `categories` (
|
||
`id` INT AUTO_INCREMENT PRIMARY KEY,
|
||
`name` VARCHAR(100) NOT NULL,
|
||
`description` TEXT,
|
||
`created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
`updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
|
||
-- 商品テーブルの作成（カテゴリとの1対多の関係）
|
||
CREATE TABLE `products` (
|
||
`id` INT AUTO_INCREMENT PRIMARY KEY,
|
||
`category_id` INT NOT NULL,
|
||
`name` VARCHAR(200) NOT NULL,
|
||
`description` TEXT,
|
||
`price` DECIMAL(10, 2) NOT NULL,
|
||
`stock` INT NOT NULL DEFAULT 0,
|
||
`created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
`updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
FOREIGN KEY (`category_id`) REFERENCES `categories`(`id`) ON DELETE CASCADE
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
|
||
-- インデックスの作成
|
||
CREATE INDEX `idx_category_id` ON `products`(`category_id`);
|
||
CREATE INDEX `idx_product_name` ON `products`(`name`);
|