Does vertica support ON CASCADE DELETE on foreign key constraint?

We have 2 tables - T1 and T2 and there is a one to many relationship between T1 and T2. How can we specify cascading delete on foreign key constraint i.e. when a record from T1 is deleted, all associated records in T2 should also be deleted?

Best Answer

  • Accepted Answer

    @marcothesane said:
    No. And it would have surprised me if it did:

      1 CREATE TABLE par (                                                                                                                                                       
      2   par_id INT NOT NULL PRIMARY KEY
      3 , par_nm VARCHAR(32)
      4 );
      5 CREATE TABLE chi (
      6   chi_id INT NOT NULL PRIMARY KEY
      7 , par_id INT NOT NULL REFERENCES par(par_id) ON DELETE CASCADE
      8 , chi_nm VARCHAR(32)
      9 );
     10 -- out time: first fetch (0 rows): 0.437 ms. all rows formatted: 0.443 ms
     11 -- out vsql:/home/gessnerm/._vfv.sql:5: error 4229:  on delete actions other than no action are not supported for foreign key constraints
    

    Vertica is a Big Data database, optimized for fast querying , fast loading of massive volumes of data. You should take advantage of that fact by using dimensional - star-schema designs.
    Referential Integrity triggers like ON DELETE CASCADE or ON UPDATE RESTRICT pertain to OLTP database work, and Vertica is an ideal OLAP type database - you can't have both, really, and choose wisely between a racing or a mountain bycicle ...

    Ok, Thanks for the update.

Answers

  • marcothesanemarcothesane Employee

    No. And it would have surprised me if it did:

      1 CREATE TABLE par (                                                                                                                                                       
      2   par_id INT NOT NULL PRIMARY KEY
      3 , par_nm VARCHAR(32)
      4 );
      5 CREATE TABLE chi (
      6   chi_id INT NOT NULL PRIMARY KEY
      7 , par_id INT NOT NULL REFERENCES par(par_id) ON DELETE CASCADE
      8 , chi_nm VARCHAR(32)
      9 );
     10 -- out time: first fetch (0 rows): 0.437 ms. all rows formatted: 0.443 ms
     11 -- out vsql:/home/gessnerm/._vfv.sql:5: error 4229:  on delete actions other than no action are not supported for foreign key constraints
    

    Vertica is a Big Data database, optimized for fast querying , fast loading of massive volumes of data. You should take advantage of that fact by using dimensional - star-schema designs.
    Referential Integrity triggers like ON DELETE CASCADE or ON UPDATE RESTRICT pertain to OLTP database work, and Vertica is an ideal OLAP type database - you can't have both, really, and choose wisely between a racing or a mountain bycicle ...

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.