I’m using MariaDB and have 2 tables I’m trying to map together into another table. I’ve done foreign key relationships before, but never with a primary key from two tables. How do I go about creating the constraint?
Order_ID (Primary Key) Order_Amount
Customer_ID (Primary Key) Customer_Name
Table 3: Mapping Table
Order_ID Customer_ID Primary Key (Order_ID & Customer_ID)
When the customer table row is deleted, I want the map to be deleted to that order, but not the order table row.
Would something like this work?
ALTER TABLE `map_table` ADD CONSTRAINT `fk_customer_id` FOREIGN KEY (`customer_id`) REFERENCES `customers`(`customer_id`) ON DELETE CASCADE ON UPDATE CASCADE;