CODEWITHSUNDEEP
postgresqltriggersinsert
Martin
Martin

Reputation: 621

PostgreSQL ON INSERT CASCADE

I've got two tables - one is Product and one is ProductSearchResult.

Whenever someone tries to Insert a SearchResult with a product that is not listed in the Product table the foreign key constrain is violattet, hence i get an error.

I would like to know how i could get my database to automatically create that missing Product in the Product Table (Just the ProductID, all other attributes can be left blank)

Is there such thing as CASCADE ON INSERT? If there is, i was not able not get it working.

Rules are getting executed after the Insert, so because we get an Error beforehand there are useless if you USE an "DO ALSO". If you use "DO INSTEAD" and add the INSERT Command at the End you end up with endless recursion.

I reckon a Trigger is the way to go - but all my attempts to write one failed.

Any recommendations?

The Table Structure: 

CREATE TABLE Product (
    ID char(10) PRIMARY KEY,
    Title varchar(150),
    Manufacturer varchar(80),
    Category smallint,
    FOREIGN KEY(Category) REFERENCES Category(ID) ON DELETE CASCADE);


CREATE TABLE ProductSearchResult (
    SearchTermID smallint NOT NULL,
    ProductID char(10) NOT NULL,
    DateFirstListed date NOT NULL DEFAULT current_date,
    DateLastListed date NOT NULL DEFAULT current_date,
    PRIMARY KEY (SearchTermID,ProductID),
    FOREIGN KEY (SearchTermID) REFERENCES SearchTerm(ID) ON DELETE CASCADE,
    FOREIGN KEY (ProductID) REFERENCES Product ON DELETE CASCADE);

Upvotes: 2

Views: 8538

Answers (2)

wildplasser
wildplasser

Reputation: 44250

In any case (rules OR triggers) the insert needs to create a new key (and new values for the attributes) in the products table. In most cases, this implies that a (serial,sequence) surrogate primary key should be used in the products table, and that the "real world" product_id ("product number") should default to NULL, and be degraded to a candidate key.

BTW: a rule can be used, rules just are tricky to implement correctly for N:1 relations (they need the same kind of EXISTS-logic as in Bart's answer above).

Maybe cascading on INSERT is not such a good idea after all. What do you want to happen if someone inserts a ProductSearchResult record for a not-existing product? [IMO a FK is always a domain; you cannot just extend a domain just by referring to a not-existant value for it; that would make the FK constraint meaningless]

Upvotes: 0

bart
bart

Reputation: 7767

Yes, triggers are the way to go. But before you can start to use triggers in plpgsql, you have to enable the language. As user postgres, run the command createlang with the proper parameters.

Once you've done that, you have to

  1. Write function in plpgsql
  2. create a trigger to invoke that function

See example 39-3 for a basic example.

Note that a function body in Postgres is a string, with a special quoting mechanism: 2 dollar signs with an optional word in between them, as the quotes. (The word allows you to quote other similar quotes.)

Also note that you can reuse a trigger procedure for multiple tables, as long as they have the columns your procedure uses.

So the function has to

  1. check if the value of NEW.ProductID exists in the ProductSearchResult table, with a select statement (you ought to be able to use SELECT count(*) ... INTO someint, or SELECT EXISTS(...) INTO somebool)
  2. if not, insert a new row in that table

If you still get stuck, come back here.

Upvotes: 2

Related Questions