Does vertica support ON CASCADE DELETE on foreign key constraint?

rajatpaliwal86

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?

rajatpaliwal86

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

marcothesane

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