Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

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

  • 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.