0

let's say I have these two tables:

CREATE TABLE `tblsuppliers` (
  `supplierID` int(11) NOT NULL AUTO_INCREMENT,
  `supplierName` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `phoneNumber` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `emailAddress` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `physicalAddress` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`supplierID`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE `tblorders` (
  `orderID` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `supplierID` int(11) NOT NULL,
  `productID` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `dateOrdered` date DEFAULT NULL,
  `dateArriving` date DEFAULT NULL,
  `isJob` bit(1) NOT NULL,
  `username` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `quantity` decimal(10,0) NOT NULL,
  `hasArrived` bit(1) DEFAULT NULL,
  PRIMARY KEY (`orderID`),
  KEY `tblorders_FK_1` (`username`),
  KEY `tblorders_FK_2` (`productID`),
  KEY `tblorders_FK` (`supplierID`),
  CONSTRAINT `tblorders_FK` FOREIGN KEY (`supplierID`) REFERENCES `tblsuppliers` (`supplierID`) ON UPDATE CASCADE,
  CONSTRAINT `tblorders_FK_1` FOREIGN KEY (`username`) REFERENCES `tblusers` (`username`) ON UPDATE CASCADE,
  CONSTRAINT `tblorders_FK_2` FOREIGN KEY (`productID`) REFERENCES `tblproducts` (`productID`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

I want to be able to delete a Supplier but keep their records in Orders, as of now, i'm not able to, I get that exception. I've been looking for a solution here for the last couple of days but as of yet, i've only found that the constraints were the other way around

I'm pretty sure i'm missing a fundamental concept and I'm aware that this type of question has been asked a lot of times here, but from the explanations, I know i'm doing something wrong, just dont know what. I'd appreciate it if I got an explanation of what it was. Thank you

carlo_webber124
  • 79
  • 7
  • If you delete a supplier where do you want the **dangling orders** to point to? You could set the pointer to 1) null, 2) a default [always existing] supplier, 3) a specific supplier set by hand, or just delete the orders (but you don't want this). – The Impaler Apr 20 '21 at 14:37
  • Alternatively, what most industry-grade apps do, is to soft-delete the supplier (use a status column to mark the supplier as deleted). You don't really delete it from the database. This way you keep all history of data and you can perform queries like "Who where the best suppliers three years ago?". If you hard-delete the data you won't be able to get that info. – The Impaler Apr 20 '21 at 14:39
  • I see. That makes sense. I shall create a new column with the status. Thank you – carlo_webber124 Apr 21 '21 at 09:08

0 Answers0