The following SQL for a Point of Sale database for a grocery store is not properly constructed. As result, when student attempts to populate the tables, it gives a foreign key constraint fail error. Modify the SQL so that it models the depicted schema and follows the following constraints: They is a one-to-many relationship between the salesdetails.SalesNumber and sales.SalesNumber fields. There is a one-to-many relationship between the products.prodid and salesdetails.prodid fields. There is to be a delete constraint on the product.prodid field a record in the product table should not be allowed to be deleted if they exist a matching prodid in the salesdetails table. Primary and foreign keys should be implemented in the appropriate tables. Referential Integrity should be enforce at the database level, where appropriate for example, if a sales record is deleted all associated records in the saledetails table should be deleted as well, etc. CREATE TABLE `products` ( `number` int(11) NOT NULL, `prodid` varchar(20) DEFAULT NULL, `prodname` varchar(30) DEFAULT NULL, `price` decimal(10,2) DEFAULT NULL, `onhand` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; -- -------------------------------------------------------- -- -- Table structure for table `sales` -- CREATE TABLE `sales` ( `SalesNumber` int(10) NOT NULL, `SalesDate` date DEFAULT NULL, `SalesTotal` decimal(10,2) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; -- -------------------------------------------------------- -- -- Table structure for table `salesdetails` -- CREATE TABLE `salesdetails` ( `number` int(10) NOT NULL, `SalesNo` int(10) DEFAULT NULL, `prodid` varchar(20) DEFAULT NULL, `price` decimal(7,2) DEFAULT NULL, `qty` int(10) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; -- -- Indexes for dumped tables -- -- -- Indexes for table `products` -- ALTER TABLE `products` ADD PRIMARY KEY (`number`), ADD UNIQUE KEY `prodid` (`prodid`); -- -- Indexes for table `sales` -- ALTER TABLE `sales` ADD PRIMARY KEY (`SalesNumber`); -- -- Indexes for table `salesdetails` -- ALTER TABLE `salesdetails` ADD PRIMARY KEY (`number`), ADD UNIQUE KEY `productid` (`prodid`), ADD KEY `fk_salesdetails_sales` (`SalesNo`); -- -- AUTO_INCREMENT for dumped tables -- -- -- AUTO_INCREMENT for table `products` -- ALTER TABLE `products` MODIFY `number` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=747; -- -- AUTO_INCREMENT for table `sales` -- ALTER TABLE `sales` MODIFY `SalesNumber` int(10) NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `salesdetails` -- ALTER TABLE `salesdetails` MODIFY `number` int(10) NOT NULL AUTO_INCREMENT; -- -- Constraints for dumped tables -- -- -- Constraints for table `products` -- ALTER TABLE `products` ADD CONSTRAINT `fk_salesdetails_product` FOREIGN KEY (`prodid`) REFERENCES `salesdetails` (`prodid`); -- -- Constraints for table `salesdetails` -- ALTER TABLE `salesdetails` ADD CONSTRAINT `fk_salesdetails_products` FOREIGN KEY (`prodid`) REFERENCES `products` (`prodid`) ON DELETE CASCADE, ADD CONSTRAINT `fk_salesdetails_sales` FOREIGN KEY (`SalesNo`) REFERENCES `sales` (`SalesNumber`) ON DELETE CASCADE; COMMIT;
The following SQL for a Point of Sale
They is a one-to-many relationship between the salesdetails.SalesNumber and
sales.SalesNumber fields.
There is a one-to-many relationship between the products.prodid and salesdetails.prodid fields.
There is to be a delete constraint on the product.prodid field a record in the product table should not be allowed to be deleted if they exist a matching prodid in the salesdetails table.
Primary and foreign keys should be implemented in the appropriate tables.
Referential Integrity should be enforce at the database level, where appropriate for example, if a sales record is deleted all associated records in the saledetails table should be deleted as well, etc.
CREATE TABLE `products` (
`number` int(11) NOT NULL,
`prodid` varchar(20) DEFAULT NULL,
`prodname` varchar(30) DEFAULT NULL,
`price` decimal(10,2) DEFAULT NULL,
`onhand` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- --------------------------------------------------------
--
-- Table structure for table `sales`
--
CREATE TABLE `sales` (
`SalesNumber` int(10) NOT NULL,
`SalesDate` date DEFAULT NULL,
`SalesTotal` decimal(10,2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- --------------------------------------------------------
--
-- Table structure for table `salesdetails`
--
CREATE TABLE `salesdetails` (
`number` int(10) NOT NULL,
`SalesNo` int(10) DEFAULT NULL,
`prodid` varchar(20) DEFAULT NULL,
`price` decimal(7,2) DEFAULT NULL,
`qty` int(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
--
-- Indexes for dumped tables
--
--
-- Indexes for table `products`
--
ALTER TABLE `products`
ADD PRIMARY KEY (`number`),
ADD UNIQUE KEY `prodid` (`prodid`);
--
-- Indexes for table `sales`
--
ALTER TABLE `sales`
ADD PRIMARY KEY (`SalesNumber`);
--
-- Indexes for table `salesdetails`
--
ALTER TABLE `salesdetails`
ADD PRIMARY KEY (`number`),
ADD UNIQUE KEY `productid` (`prodid`),
ADD KEY `fk_salesdetails_sales` (`SalesNo`);
--
-- AUTO_INCREMENT for dumped tables
--
--
-- AUTO_INCREMENT for table `products`
--
ALTER TABLE `products`
MODIFY `number` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=747;
--
-- AUTO_INCREMENT for table `sales`
--
ALTER TABLE `sales`
MODIFY `SalesNumber` int(10) NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `salesdetails`
--
ALTER TABLE `salesdetails`
MODIFY `number` int(10) NOT NULL AUTO_INCREMENT;
--
-- Constraints for dumped tables
--
--
-- Constraints for table `products`
--
ALTER TABLE `products`
ADD CONSTRAINT `fk_salesdetails_product` FOREIGN KEY (`prodid`) REFERENCES `salesdetails` (`prodid`);
--
-- Constraints for table `salesdetails`
--
ALTER TABLE `salesdetails`
ADD CONSTRAINT `fk_salesdetails_products` FOREIGN KEY (`prodid`) REFERENCES `products` (`prodid`) ON DELETE CASCADE,
ADD CONSTRAINT `fk_salesdetails_sales` FOREIGN KEY (`SalesNo`) REFERENCES `sales` (`SalesNumber`) ON DELETE CASCADE;
COMMIT;
Step by step
Solved in 4 steps
