Блог

Не уверен, что я здесь делаю не так. Я пытаюсь понять отношения между таблицами? Пожалуйста, помогите, спасибо

 DROP DATABASE IF EXISTS `sql_retail`;
CREATE DATABASE `sql_retail`; 
USE `sql_retail`;

SET NAMES utf8 ;
SET character_set_client = utf8mb4 ;

CREATE TABLE `RETAIL_ORDER` (
  `OrderNumber` int(4) NOT NULL,
  `StoreNumber` int(2) NOT NULL,
  `StoreZip`    char(9) NOT NULL,
  `OrderMonth`  char(12) NOT NULL,
  `OrderYear`   int(4) NOT NULL,
  `OrderTotal`  float(10) NOT NULL,
  
  PRIMARY KEY (`OrderNumber`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
INSERT INTO `RETAIL_ORDER` VALUES (1000, 10, 98110, 'December', 2017, 445.00);
INSERT INTO `RETAIL_ORDER` VALUES (2000, 20, 02335, 'December', 2017, 310.00);
INSERT INTO `RETAIL_ORDER` VALUES (3000, 10, 98110, 'January', 2018, 480.00);

CREATE TABLE `ORDER_ITEM` (
  `OrderNumber` int(11) NOT NULL,
  `SKU` int(7) NOT NULL,
  `Quantity` int(6) NOT NULL,
  `Price` float(10) NOT NULL,
  `Extended Price` float(10) NOT NULL,
  
  PRIMARY KEY (`OrderNumber`),FOREIGN KEY(`OrderNumber`)REFERENCES RETAIL_ORDER(`OrderNumber`),
  FOREIGN KEY(`SKU`)REFERENCES SKU_DATA(`SKU`)
 
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
INSERT INTO `ORDER_ITEM` VALUES (1000, 201000, 1, 300.00, 300.00);
INSERT INTO `ORDER_ITEM` VALUES (1000, 202000, 1, 130.00, 130.00);
INSERT INTO `ORDER_ITEM` VALUES (2000, 101100, 4, 50.00, 200.00);
INSERT INTO `ORDER_ITEM` VALUES (2000, 101200, 2, 50.00, 100.00);
INSERT INTO `ORDER_ITEM` VALUES (3000, 100200, 1, 300.00, 300.00);
INSERT INTO `ORDER_ITEM` VALUES (3000, 101100, 2, 50.00, 100.00);
INSERT INTO `ORDER_ITEM` VALUES (3000, 101200, 1, 50.00, 50.00);

CREATE TABLE `SKU_DATA` (
  `SKU` int(11) NOT NULL,
  `SKU_Description` varchar(50) NOT NULL,
  `Department` varchar(20) NOT NULL,
  `Buyer` varchar(15) NOT NULL,
  
  
  PRIMARY KEY (`SKU`),FOREIGN KEY(`Buyer`)REFERENCES BUYER(`BuyerName`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
INSERT INTO `SKU_DATA` VALUES (100100, 'Std. Scuba Tank, Yellow', 'Water Sports', 'Peter Hansen');
INSERT INTO `SKU_DATA` VALUES (100200, 'Std. Scuba Tank, Magenta', 'Water Sports', 'Peter Hansen');
INSERT INTO `SKU_DATA` VALUES (100300, 'Std. Scuba Tank, Light Blue', 'Water Sports', 'Peter Hansen');
INSERT INTO `SKU_DATA` VALUES (100400, 'Std. Scuba Tank, Dark Blue', 'Water Sports', 'Peter Hansen');
INSERT INTO `SKU_DATA` VALUES (100500, 'Std. Scuba Tank, Light Green', 'Water Sports', 'Peter Hansen');
INSERT INTO `SKU_DATA` VALUES (100600, 'Std. Scuba Tank, Dark Green', 'Water Sports', 'Peter Hansen');
INSERT INTO `SKU_DATA` VALUES (101100, 'Dive Mask, Small Clear', 'Water Sports', 'Nancy Meyers');
INSERT INTO `SKU_DATA` VALUES (101200, 'Dive Mask, Med Clear', 'Water Sports', 'Nancy Meyers');
INSERT INTO `SKU_DATA` VALUES (201000, 'Half dome tent', 'camping', 'Cindy Lo');
INSERT INTO `SKU_DATA` VALUES (202000, 'Half dome tent vestibule', 'camping', 'Cindy Lo');
INSERT INTO `SKU_DATA` VALUES (203000, 'Half dome tent vestibule wide', 'camping', 'Cindy Lo');
INSERT INTO `SKU_DATA` VALUES (301000, 'Light fly climbing harness', 'climbing', 'Jerry Martin');
INSERT INTO `SKU_DATA` VALUES (302000, 'Locking Carabiner, Oval', 'climbing', 'Jerry Martin');

CREATE TABLE `BUYER` (
  `BuyerName` varchar(15) NOT NULL,
  `Department` varchar(15) NOT NULL,
  `Position` varchar(15) NOT NULL,
  `Supervisor` varchar(15) NOT NULL,
  
  
  PRIMARY KEY (`BuyerName`),FOREIGN KEY(`Supervisor`)REFERENCES BUYER(`BuyerName`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
INSERT INTO `SKU_DATA` VALUES ('Cindy Lo', 'Purchasing', 'Buyer 2', 'Mary Smith');
INSERT INTO `SKU_DATA` VALUES ('Jerry Martin', 'Purchasing', 'Buyer 1', 'Cindo Lo');
INSERT INTO `SKU_DATA` VALUES ('Mary Smith', 'Purchasing', 'Manager','' );
INSERT INTO `SKU_DATA` VALUES ('Nancy Meyers', 'Purchasing', 'Buyer 1', 'Pete Hansen');
INSERT INTO `SKU_DATA` VALUES ('Pete Hansen', 'Purchasing', 'Buyer 3', 'Mary Smith');
 

У вас есть некоторые ошибки. Во-первых, вы назначаете внешний ключ таблице, которая еще не существует, а во-вторых, у вас есть дубликаты значений во вставках для первичных ключей. Я исправил создание вашей таблицы, проверьте ваши данные вставки.

Следуйте приведенному ниже подходу, чтобы создать свои таблицы, а затем выполнить вставки:

 DROP DATABASE IF EXISTS sql_retail ; 
CREATE DATABASE sql_retail ; 
USE sql_retail ;
SET NAMES utf8 ; 
SET character_set_client = utf8mb4 ;

CREATE TABLE `RETAIL_ORDER` (
  `OrderNumber` int(4) NOT NULL,
  `StoreNumber` int(2) NOT NULL,
  `StoreZip`    char(9) NOT NULL,
  `OrderMonth`  char(12) NOT NULL,
  `OrderYear`   int(4) NOT NULL,
  `OrderTotal`  float(10) NOT NULL,
  PRIMARY KEY (`OrderNumber`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


CREATE TABLE `ORDER_ITEM` (
  `OrderNumber` int(11) NOT NULL,
  `SKU` int(7) NOT NULL,
  `Quantity` int(6) NOT NULL,
  `Price` float(10) NOT NULL,
  `Extended Price` float(10) NOT NULL,
  PRIMARY KEY (`OrderNumber`),
  FOREIGN KEY(`OrderNumber`)REFERENCES RETAIL_ORDER(`OrderNumber`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


CREATE TABLE `SKU_DATA` (
  `SKU` int(11) NOT NULL,
  `SKU_Description` varchar(50) NOT NULL,
  `Department` varchar(20) NOT NULL,
  `Buyer` varchar(15) NOT NULL,
  PRIMARY KEY (`SKU`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;



ALTER TABLE ORDER_ITEM ADD FOREIGN KEY(`SKU`)REFERENCES SKU_DATA(`SKU`);


CREATE TABLE `BUYER` (
  `BuyerName` varchar(15) NOT NULL,
  `Department` varchar(15) NOT NULL,
  `Position` varchar(15) NOT NULL,
  `Supervisor` varchar(15) NOT NULL,
  PRIMARY KEY (`BuyerName`),
  FOREIGN KEY(`Supervisor`)REFERENCES BUYER(`BuyerName`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


ALTER TABLE SKU_DATA ADD FOREIGN KEY(`Buyer`)REFERENCES BUYER(`BuyerName`);
 

Демо: https://www.db-fiddle.com/f/7yUJcuMJPncBBnrExKbzYz/28