user1988999
user1988999

Reputation: 11

Oracle Delete "Many-to-Many" relationship table data

I have 3 tables, which are in a many-to-many relationship. I.e:

 Create Table Product(ProductId number(18,0) NOT NULL);

 Create Table Customer(CustomerId number(18,0) NOT NULL);

 Create Table CustomerProduct(CustomerId number(18,0) NOT NULL,ProductId number(18,0) NOT NULL);

Since CustomerProduct table is referencing both Product and Customer table. I am trying to delete the data from CustomerProduct table.

I only can find something like:

DELETE FROM
(
   SElECT CustomerProduct.* FROM CustomerProduct
   INNER JOIN Product ON Product.ProductId = CustomerProduct.ProductId
   INNER JOIN Customer ON Customer.CustomerId = CustomerProduct.CustomerId
   WHERE Product.ProductId = 1 AND Customer.CustomerId = 7

);

NOTE: there is no CASCADE delete defined on foreign keys... Oracle doesn't allow me to do something I can do in SQL SERVER

DELETE A
FROM A
INNER JOIN B on a.Id = b.id
WHERE b.Id = 2.....

Upvotes: 1

Views: 1590

Answers (3)

Robert Gabriel
Robert Gabriel

Reputation: 1190

I came across the same problem. I resolved very nice with a trigger.

create or replace TRIGGER "AUTO_DELETE_PRODUCTS" 
BEFORE DELETE ON Product 
FOR EACH ROW
BEGIN
  DELETE FROM CustomerProduct WHERE CustomerProduct.ProductId = :old.ProductId;
END;

You can can do this for the rest of the tables. And this way when you delete a record in one of them the info will be deleted all over the tables.

Upvotes: 1

Kern
Kern

Reputation: 103

I just spent a couple hours trying to figure this out and you have the correct answer in your question. Though, if you change your query to where the relevant conditions do not exist in the relationship table then it makes a bit more sense why the join would be needed.

Something like:

CREATE TABLE Product(ProductId number(18,0) NOT NULL, Name VARCHAR2(30));
CREATE TABLE Customer(CustomerId number(18,0) NOT NULL, Name VARCHAR2(30));

DELETE FROM
    (SELECT cp.* 
       FROM CustomerProduct cp
       INNER JOIN Product p ON p.ProductId = cp.ProductId
       INNER JOIN Customer c ON c.CustomerId = cp.CustomerId
       WHERE c.Name LIKE 'TEST%' AND p.Name IS NULL);

Upvotes: 0

Colin
Colin

Reputation: 11

I don't understand what you want to do. Deleting from table CustomerProduct can be done with a simple delete statement as follows:

  delete CustomerProduct 
  where ProductId = 1 and CustomerId  = 7

Upvotes: 1

Related Questions